如何查看執行失敗的JOB清單以及其詳細訊息

問題：有甚麼方法可以快速查看執行失敗的JOB清單以及其詳細訊息呢？

解答：可以使用msdb的sysjobhistory、sysjobsteps與sysjobs，下表為簡略的說明。

SQL Server Agent Tables
sysjobhistory	Contains information about the execution of scheduled jobs by SQL Server Agent
sysjobsteps	Contains the information for each step in a job to be executed by SQL Server Agent
sysjobs	Stores the information for each scheduled job to be executed by SQL Server Agent

--如何取得失敗JOB的詳細錯誤訊息

DECLARE @NumDays INT

DECLARE @FinalDate VARCHAR(8)

SET @NumDays=-1

set @FinalDate=CONVERT(VARCHAR(8), (SELECT DATEADD (DAY,(@NumDays), GETDATE())), 112) 

SELECT

   J.name,

   J.[description],

   S.step_id,

   S.step_name,

   H.run_date,

   H.run_time,  

   H.[message]

FROM msdb..sysjobhistory H

INNER JOIN msdb..sysjobsteps S

ON H.step_id = S.step_id AND H.job_id = S.job_id

INNER JOIN msdb..sysjobs J

ON J.job_id = H.job_id

WHERE  h.run_status = 0 AND run_date >=  @FinalDate

ORDER BY h.instance_id DESC

--結果

參考網址:
Failed SQL Server Agent Jobs
SQL Server Agent Tables