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

2 comments:

T. Johnson said...

Thx, I like the script

Richard Rozema 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

Post a Comment