Tracking SQL Server Agent Job Failures with dbo.sysjobhistory
The dbo.sysjobhistory system table, located in the msdb database, stores historical details about the execution of SQL Server Agent jobs. This table is essential for administrators who need to review past job executions, especially for identifying and troubleshooting failures.
Query: Retrieve Failed Job Steps
The SQL query below extracts information about failed SQL Server Agent job steps. It includes job names, step details, severity, failure messages, run dates and times, and the execution status.
-->> Job and Step Failure Details
SELECT
j.name AS job_name,
js.step_name,
jh.sql_severity,
jh.message,
jh.run_date,
jh.run_time,
MSDB.dbo.agent_datetime(jh.run_date, jh.run_time) AS date_time,
CASE jh.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In progress'
END AS run_status
FROM
msdb.dbo.sysjobs AS j
INNER JOIN
msdb.dbo.sysjobsteps AS js ON js.job_id = j.job_id
INNER JOIN
msdb.dbo.sysjobhistory AS jh ON jh.job_id = j.job_id AND jh.step_id = js.step_id
WHERE
jh.run_status = 0
AND CAST(jh.run_date AS INT) > 20200318;
This query is especially useful for identifying job step failures after a specific date (20200318 in this example). The agent_datetime function is used to convert the run date and time into a more readable datetime format.
Tip: About agent_datetime
The agent_datetime function is a built-in MSDB utility that combines the run_date and run_time columns into a standard DATETIME value, making it easier to interpret scheduling data.
Additional Resources
-
Microsoft Documentation on
sysjobhistory:
sysjobhistory Documentation
