Yesterday my long time customer from New Zealand asked me during our regular weekly meet of Comprehensive Database Performance Health Check if I know a T-SQL Script to Check SQL Server Job History.
Absolutely, here is the simple script, I use to check all the SQL Server Agent Job History. Please note that while writing this script, I have taken help of the function agent_datetime which I have blogged earlier a few years ago.
Let us see the T-SQL Script:
SELECT jobs.name AS 'JobName', msdb.dbo.agent_datetime(run_date, run_time) AS 'Run Date Time', history.run_duration AS 'Duration in Second' FROM msdb.dbo.sysjobs jobs INNER JOIN msdb.dbo.sysjobhistory history ON jobs.job_id = history.job_id WHERE jobs.enabled = 1
The script is very simple and it returns details of all the enabled job. You can also remove the last where condition and it will return all the jobs on your system. While doing SQL Server performance tuning, I often ask my client questions about the jobs which are running currently on their server. My experience says there are always few jobs which no one knows why are running on the server and often they negatively impact the performance.
Here are few additional blog posts related to SQL Server Job History:
- SQL SERVER – How to Get SQL Server Agent Properties?
- How to Find Service Account for SQL Server and SQL Server Agent? – Interview Question of the Week #179
- SQL SERVER – SQL Server Agent Not Starting – Failed to Initialize SQL Agent Log (Reason: Access is Denied).
- How to List All the SQL Server Jobs When Agent is Disabled? – Interview Question of the Week #171
- SQL SERVER – SQL Server Agent Missing in SQL Server Management Studio (SSMS)
- SQL SERVER – Execution Failed. See the Maintenance Plan and SQL Server Agent Job History Logs for Details
Reference: Pinal Dave (https://blog.sqlauthority.com)
Here is a script that I created and which goes in the same direction. It shows and overview of all jobs and gives some statistic informations.
WITH JobCounters AS
,MIN(run_duration) AS MinRunDurationSeconds
,MAX(run_duration) AS MaxRunDurationSeconds
,MAX([msdb].[dbo].[agent_datetime]([run_date], [run_time])) AS LastStartDateTime
,AVG(run_duration) AS AvgRunDurationSeconds
WHERE step_id = 0 — Job Outcome
GROUP BY job_id
J.[name] AS JobName
,JC.[name] AS JobCategory
,CAST((CASE WHEN J.[enabled] = 1 THEN 1 ELSE 0 END) AS BIT) AS IsEnabled
WHEN LastJobExecution.run_status = 2 THEN ‘RETRY_STEP’
WHEN JA.job_id IS NOT NULL AND JA.stop_execution_date IS NULL AND JA.start_execution_date IS NOT NULL THEN ‘EXECUTING…’
WHEN LastJobExecution.run_status = 0 THEN ‘ERROR’
WHEN LastJobExecution.run_status = 1 THEN ‘SUCCESS’
WHEN LastJobExecution.run_status = 3 THEN ‘ABORT’
END) AS LastRunStatus
,COALESCE(JA.start_execution_date, JobCounters.LastStartDateTime) AS LastStartDateTime
,JA.next_scheduled_run_date AS NextStartDateTime
,(CASE JA.run_requested_source WHEN 1 THEN ‘SQL Agent’ WHEN 2 THEN ‘Alarming’ WHEN 3 THEN ‘Boot’ WHEN 4 THEN ‘User’ ELSE JA.run_requested_source END) AS StartedBy
,JS.step_name AS LastJobStep
,LastJobExecution.run_duration AS LastRunDurationSeconds
FROM [msdb].[dbo].[sysjobs] AS J
LEFT JOIN [msdb].[dbo].[syscategories] AS JC ON J.category_id = JC.category_id
LEFT JOIN JobCounters ON J.job_id = JobCounters.job_id
LEFT JOIN [msdb].[dbo].[sysjobactivity] JA ON J.job_id = JA.job_id
LEFT JOIN [msdb].[dbo].[sysjobsteps] JS ON JA.job_id = JS.job_id AND JA.last_executed_step_id = JS.step_id
LEFT JOIN (
SELECT run_duration, run_status, step_id, job_id, run_date, run_time, retries_attempted, [message], ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY instance_id DESC) AS RowId
) AS LastJobExecution ON J.job_id = LastJobExecution.job_id AND ISNULL(LastJobExecution.RowId, 1) = 1
JA.session_id IS NULL
OR EXISTS (SELECT NULL FROM [msdb].[dbo].[sysjobactivity] GROUP BY job_id HAVING job_id = J.job_id AND MAX(session_id) = JA.session_id)
ORDER BY J.[name];
Thanks for sharing it.
Thanks so much for sharing such valuable script
OK, from my experience, the run duration is not in seconds. The run duration is a representation of the time run, with the specific separators removed. For example, I have a job that runs for just over an hour. The run duration is represented as 10212 via the script above.
This value represents 01:02:12, being 1 hour, 2 minutes and 12 seconds.
Please keep this in mind when using this script.
I need a small help in order to improve my knowledge. I have archive the SSIS job history of all SSIS jobs rather than purging it. At the time of requirement, we can restore the history from Archived table. In that case do we need to Archive the data of only table [msdb].[dbo].[sysjobhistory], OR any other table I have to include in Archival Activity? If someone can confirm, that will be helpful.
Thanks in advance.