SQL Server Script to Display Job History

Download PDF

Its not always quick and easy in SQL Server to get a full list of the jobs that have been run, when they were run and how long they took. I created the following script to quickly check on the status and run time of SQL Server maintenance plans and jobs.

Display Job History

;WITH jobListCTE as
(
  SELECT j.name as job_name, 
		 msdb.dbo.agent_datetime(run_date, run_time) AS run_datetime,
         RIGHT('000000' + CONVERT(varchar(6), run_duration), 6) AS run_duration,
		 message
    FROM msdb..sysjobhistory h
   INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
   WHERE h.step_name = '(Job outcome)'
) 
SELECT job_name as [JobStep], 
       run_datetime as [StartDateTime], 
	   SUBSTRING(run_duration, 1, 2) + ':' + 
	   SUBSTRING(run_duration, 3, 2) + ':' + 
	   SUBSTRING(run_duration, 5, 2) as [Duration], 
	   message
  FROM jobListCTE
 ORDER BY run_datetime DESC, job_name;

Just run the script in SQL Server Management Studio to get your job history ordered descending by when the job was run.

 

More from Stedman Solutions:

SteveStedman5
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!

3 Comments on “SQL Server Script to Display Job History

  1. this just show the last running during of the jobs, anyway to show how many time it fails during the last month ?

    • I wrote this some time ago to capture failed jobs:

      SELECT j.name,
      msdb.dbo.agent_datetime(run_date, run_time),
      h.step_id,
      h.step_name,
      h.sql_message_id,
      h.sql_severity,
      h.message,
      ((run_duration / 10000 * 3600 + (run_duration / 100) % 100 * 60 + run_duration % 100 + 31) / 60)
      FROM msdb..sysjobhistory h
      JOIN msdb..sysjobs j ON j.job_id = h.job_id
      WHERE msdb.dbo.agent_datetime(run_date, run_time) >= ‘2016-05-01’
      AND msdb.dbo.agent_datetime(run_date, run_time) <= GETDATE() AND j.enabled = 1 AND h.run_status <> 1
      ORDER BY j.name,
      h.run_date,
      h.step_id,
      h.message;

Leave a Reply

Your email address will not be published. Required fields are marked *

*