SQL Server Agent Job History can be queried from msdb. A New Zealand customer requested this report during our weekly health check.
During tuning consultations, I ask which jobs are running and why. I’ve encountered jobs whose purpose the team couldn’t explain. Their workload can matter to the investigation.
SELECT jobs.name AS JobName, history.step_id, history.step_name,
msdb.dbo.agent_datetime(history.run_date, history.run_time) AS RunDateTime,
(history.run_duration / 10000) * 3600
+ ((history.run_duration % 10000) / 100) * 60
+ (history.run_duration % 100) AS DurationSeconds,
history.run_status, history.message
FROM msdb.dbo.sysjobs AS jobs
JOIN msdb.dbo.sysjobhistory AS history ON history.job_id = jobs.job_id
WHERE jobs.enabled = 1 AND history.run_date > 0
ORDER BY history.instance_id DESC;My original script incorrectly labeled run_duration as seconds. Agent encodes it as hours, minutes and seconds. The calculation converts that value to elapsed seconds. Hours can exceed 23, so ordinary clock conversion is inappropriate.
step_id 0 describes the job-level outcome. Other values describe steps. Keep both for messages and failures, or filter to zero for outcomes only. The enabled filter reflects the current job setting.
History follows configured retention and can be purged. It does not list currently running jobs. The example keeps the original msdb.dbo.agent_datetime helper. The linked conversion alternative is available if that helper is unsuitable.
Use the documented Agent roles and metadata permissions for the intended account. Missing visible history does not prove a job never ran. Check retention and access as well.
Related reading
- Comprehensive Database Performance Health Check
- agent_datetime
- 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
Retained job history is not current execution state, it is a record subject to filtering and retention.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
5 Comments. Leave new
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
(
SELECT
job_id
,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
FROM [msdb].[dbo].[sysjobhistory]
WHERE step_id = 0 — Job Outcome
GROUP BY job_id
)
SELECT
J.[name] AS JobName
,JC.[name] AS JobCategory
,CAST((CASE WHEN J.[enabled] = 1 THEN 1 ELSE 0 END) AS BIT) AS IsEnabled
,(CASE
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’
ELSE NULL
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
,JobCounters.MinRunDurationSeconds
,JobCounters.MaxRunDurationSeconds
,JobCounters.AvgRunDurationSeconds
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
FROM msdb.dbo.[sysjobhistory]
) AS LastJobExecution ON J.job_id = LastJobExecution.job_id AND ISNULL(LastJobExecution.RowId, 1) = 1
WHERE
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.
Hi all,
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.