以往每次看到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