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:
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!
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;
Nice query Jay. I updated it to correct some formatting issues caused by the blog commenting system.
-Steve Stedman