T-SQL Query to find last run status of scheduled Jobs | SansSQL

Wednesday, August 21, 2013

T-SQL Query to find last run status of scheduled Jobs

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"
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:

T. Johnson said...

Thx, I like the script

Unknown said...

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

Unknown said...

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

Ads