Last Successful SQL Server Agent Job Run Date and Time

SQL Server stores SQL Server Agent job history in sysjobhistory table within the msdb database. We can query the table to get the date and time of last successful SQL Server agent job(s) run. The table stores each job step history, where step_id 0 for each job actually give the overall job outcome.

The sysjobhistory table also stores each step's run date and time. However, they are being stored in separate columns, run_date and run_time. Also instead of having date and/or time data type, they have integer data type. But don't worry, there is a system function in msdb database, called agent_datetime, which can be used to convert those run_date and run_time into a datetime data type.

So, to get the date and time of the last successful SQL Server Agent job(s) run, we can use the following script:

WITH jobhistory as (   
   SELECT    job_id,
             run_status,
             last_run_time = max(dbo.agent_datetime(run_date, run_time))
   FROM      msdb.dbo.sysjobhistory
   WHERE     step_id = 0
             AND run_status = 1
   GROUP BY job_id, run_status)

SELECT    j.job_id,
          j.name,
          jh.run_status,
          jh.last_run_time
FROM      msdb.dbo.sysjobs j
          LEFT OUTER JOIN jobhistory jh
          ON j.job_id = jh.job_id
ORDER BY j.name ASC

The script should be pretty straight forward. Basically, first we build a CTE to get the last successful time of each job from the sysjobhistory table. We do this by filtering those SQL Server agent jobs that have run status equal to 1 (success) for job step with id 0. Once we do that, we then create a join of the sysjobs table (which contains information such as the job name), with the CTE.