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:

  1. T. JohnsonJune 8, 2014 at 11:14 PM

    Thx, I like the script

    ReplyDelete
  2. UnknownJanuary 10, 2017 at 4:29 PM

    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

    ReplyDelete
  3. UnknownApril 19, 2018 at 11:05 AM

    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

    ReplyDelete