4.SQLServer数据库状态监控

有很多地方可以设置定时任务，比如：Windows的计划任务，Linux下的crontab，各种开发工具里的timer组件。SQLServer也有它的定时任务组件SQLServerAgent，基于它

有很多地方可以设置定时任务，比如：Windows的计划任务，Linux下的crontab，各种开发工具里的timer组件。SQL Server也有它的定时任务组件 SQL Server Agent，基于它可以方便的部署各种数据库相关的作业(job)。

一. 作业历史纪录

作业的历史纪录按时间采用FIFO原则，当累积的作业历史纪录达到上限时，就会删除最老的纪录。

1. 作业历史纪录数配置

所有作业总计纪录条数默认为1000，最多为999999条；单个作业总计记录条数默认为100，最多为999999条。有下面2种方式可以进行修改：

(1) SSMS/SQL Server Agent/属性/历史；

(2) 未记载的扩展存储过程，SQL Server 2005及以后版本适用，以下脚本将记录数设回默认值：

EXEC msdb.dbo.sp_set_sqlagent_properties
@jobhistory_max_rows=-1,
@jobhistory_max_rows_per_job=-1
GO

2. 删除作业历史纪录

(1) SSMS/SQL Server Agent/右击作业文件夹或某个作业/查看历史纪录/清除

在SQL Server 2000中会一次清除所有作业历史记录，SQL Server 2005 及以后版本可以有选择的清除某个作业/某个时间之前的历史纪录；

(2) SQL Server 2005及以后版本，提供了系统存储过程如下：

--清除所有作业15天前的纪录
DECLARE @OldestDate datetime
SET @OldestDate = GETDATE()-15
EXEC msdb.dbo.sp_purge_jobhistory
@oldest_date=@OldestDate
--清除作业”Test”3天前的纪录
DECLARE @OldestDate datetime
DECLARE @JobName varchar(256)
SET @OldestDate = GETDATE()-3
SET @JobName = 'Test'
EXEC msdb.dbo.sp_purge_jobhistory
@job_name=@JobName,
@oldest_date=@OldestDate

作业历史纪录数有上限，通常不需要手动去删除。

3. 保留作业历史纪录

即便设置了历史记录上限到999999，如果作业很多，加之作业运行很频繁，最终历史记录还是会被慢慢删除掉。

如果想要保留某些作业历史的记录，可以打开作业属性/步骤/编辑/高级，选择将这个步骤的历史记录输出到文件/自定义表中，如下图：

二. 作业运行状态

界面上可以通过: SSMS/SQL Server Agent/右击作业文件夹或某个作业/查看历史纪录，如下用SQL 语句检查作业状态。

1. 作业上次运行状态及时长

利用系统表msdb.dbo.sysjobhistory：

(1) 表中的run_status字段表示作业上次运行状态，有0~3共4种状态值，详见帮助文档，另外在2005的帮助文档中写到：sysjobhistory的run_status为4表示运行中，经测试是错误的，在2008的帮助中已没有4这个状态；

(2) 表中run_duration字段表示作业上次运行时长，格式为HHMMSS，比如20000则表示运行了2小时。

如下脚本查看所有作业最后一次运行状态及时长：

if OBJECT_ID('tempdb..#tmp_job') is not null
drop table #tmp_job
--只取最后一次结果
select job_id,
run_status,
CONVERT(varchar(20),run_date) run_date,
CONVERT(varchar(20),run_time) run_time,
CONVERT(varchar(20),run_duration)run_duration
into #tmp_job
from msdb.dbo.sysjobhistoryjh1
where jh1.step_id = 0
and(select COUNT(1) from msdb.dbo.sysjobhistory jh2
wherejh2.step_id = 0
and(jh1.job_id = jh2.job_id)
and(jh1.instance_id <=jh2.instance_id))=1
--排除syspolicy_purge_history这个系统作业
select a.namejob_name,
case b.run_status when 0 then 'Failed' when 1 then 'Succeeded' when 2 then 'Retry' when 3 then 'Canceled'
else 'Unknown'
end as job_status,
LEFT(run_date,4)+'-'+SUBSTRING(run_date,5,2)+'-'+RIGHT(run_date,2)
+SPACE(1)
+LEFT(RIGHT(1000000+run_time,6),2)+':'
+SUBSTRING(RIGHT(1000000+run_time,6),3,2)+':'
+RIGHT(RIGHT(1000000+run_time,6),2) as job_started_time,
+LEFT(RIGHT(1000000+run_duration,6),2)+':'
+SUBSTRING(RIGHT(1000000+run_duration,6),3,2)+':'
+RIGHT(RIGHT(1000000+run_duration,6),2) as job_duration
from msdb.dbo.sysjobs a
left join #tmp_jobb
on a.job_id=b.job_id
where a.name not in('syspolicy_purge_history')
and a.enabled = 1
order by b.run_status asc,a.name,b.run_duration desc

2. 作业当前运行状态及时长

什么时候可能要检查作业的当前状态？

(1) 需要关闭SQL Server或SQL Server Agent服务时；

(2) 等到当前作业完成，有后续动作；

(3) 纯粹只是查看当前作业运行到哪个步骤等等。

通过SSMS/SQL Server Agent/右击作业文件夹或某个作业/查看历史纪录，看到的作业历史记录存放在：

select * from msdb.dbo.sysjobhistory