通常,我们比较习惯于通过SSMS来查看SQL Server代理作业的历史记录,这里将简单地介绍如何通过T-SQL查看历史记录。
通过SSMS:如下图,依次展开SQL Server代理-->作业-->具体的作业名称,然后右击并点击“查看历史记录”,即可看到对应作业的历史记录。
通过T-SQL:
SELECT [JobName] = JOB.name,
[Step] = HIST.step_id,
[StepName] = HIST.step_name,
[Message] = HIST.message,
[Status] = CASE WHEN HIST.run_status = 0 THEN 'Failed'
WHEN HIST.run_status = 1 THEN 'Succeeded'
WHEN HIST.run_status = 2 THEN 'Retry'
WHEN HIST.run_status = 3 THEN 'Canceled'
END,
[RunDate] = HIST.run_date,
[RunTime] = HIST.run_time,
[Duration] = HIST.run_duration
FROM sysjobs JOB
INNER JOIN sysjobhistory HIST ON HIST.job_id = JOB.job_id
WHERE HIST.run_date>=CONVERT(CHAR(8),GETDATE()-1,112) AND HIST.run_status<>1
/* WHERE JOB.name = 'Job_Name' */
ORDER BY HIST.run_date, HIST.run_time
通过T-SQL,我们可以很轻松地收集相关Job的历史运行情况,对于失败的Job还可以进一步给出DBA提醒。