TSQL Script to Display Agent Job History

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.

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:

• Stedman Solutions
• Job History Report in Database Health Monitor

More from Stedman Solutions:

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!