4. SQL Server数据库状态监控 - 作业状态

qwe3223678qwe

　　有很多地方可以设置定时任务，比如：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，如果作业很多，加之作业运行很频繁，最终历史记录还是会被慢慢删除掉。
　　如果想要保留某些作业历史的记录，可以打开作业属性/步骤/编辑/高级，选择将这个步骤的历史记录输出到文件/自定义表中，如下图：

http://images.cnitblog.com/blog/57957/201409/051010410321385.png

　　二. 作业运行状态
　　界面上可以通过: 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  @MaxMinutes
　　还有种比较笨的方法，在要监视的所有作业中增加一个步骤，如 : select GETDATE() 放在第一步，这样在sysjobhistory中就会有步骤1的运行纪录了，以此为起点，可以计算已运行时长。如果有很多已经部署的job，这确实不是个好办法。
　　又或者，在每个作业最后一步，放一个检查的步骤，这样所有状态时长全都监视到了，问题是如果作业运行时间过长，最后的检查步骤根本无法被运行到。
　　三. 作业状态告警
　　作业在完成后，自己有状态检查和告警机制，通常选择邮件告警，如下图：

http://images.cnitblog.com/blog/57957/201409/051017186416147.png

　　但这仅限对作业最终运行状态监视：
　　(1) 没有运行结束的作业无法告警，或者说对作业的运行时长没有监视；
　　(2) 如果作业在某个中间步骤设置了：失败后继续下一步，后续的作业步骤都成功，那么作业最终状态不会显示会失败，不会触发告警，如下脚本检查每个作业的所有步骤最后一次运行状态：
if OBJECT_ID('tempdb..#tmp_job_step') is not null　　
    drop table #tmp_job_step
　　

　　
select jh1.job_id,
　　
       jh1.step_id,
　　
       jh1.run_status,
　　
       CONVERT(varchar(20),jh1.run_date) run_date,
　　
       CONVERT(varchar(20),jh1.run_time) run_time,
　　
       CONVERT(varchar(20),jh1.run_duration) run_duration
　　
  into #tmp_job_step
　　
  from msdb.dbo.sysjobhistoryjh1
　　
where (select COUNT(1) from msdb.dbo.sysjobhistoryjh2
　　
        where (jh1.job_id = jh2.job_id and jh1.step_id = jh2.step_id)
　　
          and(jh1.instance_id