数据库日常维护-CheckList_01历史Agent Job执行情况检查

检查Agent Job中日常维护作业或业务作业是否成功，如每天的备份、碎片整理、索引维护、历史备份文件清除等，可利用SSMS工具，通过CDC下面设置好的DB Server List，运行下面脚本一次，便可以查询完成所有JOB历史运行情况。

--SQL

-- 参数定义

DECLARE @PreviousDate datetime

DECLARE @Year VARCHAR(4)

DECLARE @Month VARCHAR(2)

DECLARE @MonthPre VARCHAR(2)

DECLARE @Day VARCHAR(2)

DECLARE @DayPre VARCHAR(2)

DECLARE @FinalDate INT 

--初始化参数 

SET @PreviousDate = DATEADD(dd, -7, GETDATE()) -- Last 7 days 

SET @Year = DATEPART(yyyy, @PreviousDate) 

SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))

SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)

SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))

SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)

SET @FinalDate = CAST(@Year + @Month + @Day AS INT)

 -- 查询

SELECT   j.[name],

         s.step_name,

         h.step_id,

         h.step_name,

         h.run_date,

         h.run_time,

         h.sql_severity,

         h.message,

         h.server

FROM     msdb.dbo.sysjobhistory h

         INNER JOIN msdb.dbo.sysjobs j

           ON h.job_id = j.job_id

         INNER JOIN msdb.dbo.sysjobsteps s

           ON j.job_id = s.job_id

           AND h.step_id = s.step_id

WHERE    h.run_status = 0 --  0 is Failure, 1 is sucess

         AND h.run_date > @FinalDate

ORDER BY h.instance_id DESC