[SQL Server] Use Script to get Job Fail Message

以往每次看到Job fail的alert,都會需要一直點UI進去看fail message解決job fail的問題,但其實用script就能方便查到fail message了


 

如果只想看目前的fail message可以跑下面的script:

use [msdb]
go
select h.server as [Server],
j.[name] as [Name],
h.message as [Message],
h.run_date as LastRunDate, 
h.run_time as LastRunTime
from sysjobhistory h
inner join sysjobs j on h.job_id = j.job_id
where j.enabled = 1 
and h.instance_id in
(select max(h.instance_id)
from sysjobhistory h group by (h.job_id))
and h.run_status = 0

 

若是想看更多以往的job fail紀錄:

use [msdb]
go

select j.name
    ,js.step_name
    ,jh.sql_severity
    ,jh.message
    ,jh.run_date
    ,jh.run_time
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS js ON js.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobhistory AS jh ON jh.job_id = j.job_id 
WHERE jh.run_status = 0