TSQL Script to Display Agent Job History

Download PDF

It can be a bit time consuming to work through the the SQL Server Management Studio (SSMS) user interface to check on the agent job history to find specific agent jobs that have been run. To help speed that process up, I created the following TSQL script to directly query the job history.

This query makes use of a common table expression to first collect the details from the sysjobhistory table and the sysjobs table, with some formatting.

Agent 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.dbo.sysjobhistory h
INNER JOIN msdb.dbo.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;

You get the following output.

agent job history

 

You can now modify this query and drill down on just specific jobs out of the job history that you may be interested in.

Another option is to take a look at the job history report in the Database Health Monitor Application:

 

Related Links:

Posted in DBA Tagged with: , , , , , , ,

Leave a Reply

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

*

Time limit is exhausted. Please reload CAPTCHA.