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.

Posted in SQL Server, TSQL Tagged with: , , , ,
3 comments on “SQL Server Script to Display Job History
  1. David Wong says:

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

    • Jay says:

      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 *

*

Time limit is exhausted. Please reload CAPTCHA.