Here is a T-SQL query to find the last run status of all the scheduled jobs in SQL Server.
This query will be handy when you are not able to access the "Job Activity Monitor"
This query will be handy when you are not able to access the "Job Activity Monitor"
USE msdb GO SELECT DISTINCT SJ.Name AS JobName, SJ.description AS JobDescription, SJH.run_date AS LastRunDate, CASE SJH.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Successful' WHEN 3 THEN 'Cancelled' WHEN 4 THEN 'In Progress' END AS LastRunStatus FROM sysjobhistory SJH, sysjobs SJ WHERE SJH.job_id = SJ.job_id and SJH.run_date = (SELECT MAX(SJH1.run_date) FROM sysjobhistory SJH1 WHERE SJH.job_id = SJH1.job_id) ORDER BY SJH.run_date desc
3 comments:
Thx, I like the script
Your query only works if the job history hasn't been cleaned yet. Job Activity Monitor can still retrieve the status and last run date, even after the job history has been cleaned. For this it uses dbo.sysjobservers. A query that gives you the same results is:
select
sj.name,
sjs.last_run_outcome, -- 0 = fail, 1 = succeed, 2 = cancel
sjs.last_outcome_message,
case when sjs.last_run_date > 0
then datetimefromparts(sjs.last_run_date/10000, sjs.last_run_date/100%100, sjs.last_run_date%100, sjs.last_run_time/10000, sjs.last_run_time/100%100, sjs.last_run_time%100, 0)
end as last_run_datetime
from msdb.dbo.sysjobservers sjs
left outer join msdb.dbo.sysjobs sj on (sj.job_id = sjs.job_id)
order by sj.name
This query returns the failed/cancelled jobs as per the last run status.
USE msdb
GO
if OBJECT_ID('tempdb..#temptbl1') is not null drop table #temptbl1
go
SELECT DISTINCT sjh.server, SJ.Name AS JobName, SJH.step_id,sjh.step_name,sjh.message,
DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME) AS [LastTimeJobRanOn] ,
CASE SJH.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Successful'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS LastRunStatus
into #temptbl1 FROM sysjobhistory SJH (nolock), sysjobs SJ (nolock), sysjobschedules SJS (nolock), sysschedules ss(nolock)
WHERE SJH.job_id = SJ.job_id and sjh.step_id<>0 and sj.enabled=1 and sj.job_id=sjs.job_id and sjs.schedule_id=ss.schedule_id
and ss.enabled=1 and sjh.run_status in (0,3) and
DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME) = (SELECT MAX(DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME)) FROM sysjobhistory SJH1(nolock) WHERE SJH.job_id = SJH1.job_id)
ORDER BY LastTimeJobRanOn desc
select * from #temptbl1
Post a Comment