Friday, August 25, 2017

SQL Server Agent - Query the last run status of your jobs

Do you know how to check the last run status of your scheduled Agent jobs?  In SSMS you can go to SQL Server Agent / Jobs, and then right click the job and choose to 'View History', like this:


... but, if you've got a lot of jobs to check, that's going to take quite a bit of time.  

This script is just a very quick query to collect the status, or outcome of the last time each of your Agent jobs ran.

   USE msdb
   SELECT
       j.name [JobName],
       CASE WHEN js.last_run_outcome = 0 THEN 'FAIL'
               WHEN js.last_run_outcome = 1 THEN 'SUCCESS'
               WHEN js.last_run_outcome = 3 THEN 'CANCEL' END [Outcome],
       js.last_outcome_message [OutcomeMessage],
       CASE WHEN js.last_run_date > 0
         THEN DATETIMEFROMPARTS(js.last_run_date/10000,js.last_run_date/100%100,
           js.last_run_date%100js.last_run_time/10000,js.last_run_time/100%100
           js.last_run_time%100,0) END [LastRunDatetime]
   FROM
       dbo.sysjobservers js left outer join msdb.dbo.sysjobs j
         ON jS.job_id = j.job_id
   WHERE
       j.[enabled] = 1
   ORDER BY
       j.[name]


Should give you back something like this:












More details here for sysjobs and sysjobservers:

https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/sql-server-agent-tables-transact-sql