Listing Last Night’s Failed Agent Jobs With One msdb Query

The morning check should show which scheduled work failed, with its actual step and message. A query for failed Agent jobs gives you that list without opening every job's history window.

Read Failed Agent Jobs by Step and by Outcome

SQL Server Agent records execution history in msdb. sysjobs identifies the job, and sysjobhistory holds step and summary records. Those records answer related but different questions, so preserve their step identifiers.

A step_id greater than zero identifies a job step. A step_id of zero identifies the job's overall outcome record. A failed step can lead to another configured step and an ultimately successful job outcome.

I start with the failing step and its message. The job name identifies the responsibility, but the step tells you what failed. A general red status icon cannot explain whether the problem was a backup, command, or unavailable dependency.

This inspection requires SQL Server Agent and permission to read the relevant msdb history. SQL Server Express does not include Agent. Do not mistake a platform without that service for a server whose jobs never failed.

The first query examines the last twenty-four hours by the server's local time. That is a specific rolling window, not necessarily the previous calendar night. Adjust the boundary to the reporting period you actually need.

Convert the Stored Start Date and Time

History stores run_date and run_time as integers. msdb.dbo.agent_datetime combines them into a datetime. Use that conversion for the time filter and display it alongside the original job and step information.

DECLARE @Since datetime = DATEADD(hour, -24, GETDATE());
SELECT j.name AS JobName, h.step_id, h.step_name,
       msdb.dbo.agent_datetime(h.run_date, h.run_time) AS StepStartedAt,
       h.run_status, h.sql_message_id, h.sql_severity,
       h.message, h.instance_id
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j ON j.job_id = h.job_id
WHERE h.run_status = 0 AND h.step_id > 0
  AND h.run_date > 0
  AND msdb.dbo.agent_datetime(h.run_date, h.run_time) >= @Since
ORDER BY StepStartedAt DESC, JobName, h.step_id;

run_status 0 identifies failure. The query intentionally filters step records, keeping the message that describes the failing work. instance_id provides a useful history-row identity for later reference.

The timestamp represents the recorded start. A step beginning before your boundary but failing afterward can fall outside this query. Decide whether the operational question concerns starts, finishes, or any execution overlapping the window.

History generally appears after a step finishes. A currently stuck or still-running job needs a different inspection of current activity. The absence of a failure row does not prove that all expected work completed.

Include Retries and Cancellations

run_status 2 represents a retry and run_status 3 represents cancellation. Review those records separately from failure because the operational follow-up differs. Retried work can eventually complete while still indicating a recurring problem.

DECLARE @Since datetime = DATEADD(hour, -24, GETDATE());
SELECT j.name AS JobName, h.step_id, h.step_name,
       CASE h.run_status WHEN 2 THEN N'Retry' WHEN 3 THEN N'Canceled' END AS RecordedOutcome,
       CASE WHEN h.step_id = 0 THEN N'Job summary' ELSE N'Step record' END AS RecordScope,
       msdb.dbo.agent_datetime(h.run_date, h.run_time) AS StartedAt,
       h.message, h.instance_id
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j ON j.job_id = h.job_id
WHERE h.run_status IN (2,3) AND h.run_date > 0
  AND msdb.dbo.agent_datetime(h.run_date, h.run_time) >= @Since
ORDER BY StartedAt DESC, JobName, h.instance_id;

This query allows summary rows because a cancellation can be represented at the overall job level. Do not apply the first query's step-only filter blindly and then conclude that no jobs were canceled.

Check the retry configuration and the final outcome before assigning severity to a retry. A successful retry is useful evidence about instability. It is not the same operational result as a job that exhausted retries and stopped.

A cancellation also needs context. An administrator can stop a job deliberately, while a dependency or operational interruption can stop expected work unexpectedly. The recorded message and related operational record help distinguish those cases.

Find Failed Agent Jobs in the Final Summary

Use step_id zero when the question is which jobs ultimately failed. Keep that result beside the step details instead of counting every failing step as a separate failed job. Several history rows can belong to one job execution.

DECLARE @Since datetime = DATEADD(hour, -24, GETDATE());
SELECT j.name AS JobName,
       msdb.dbo.agent_datetime(h.run_date, h.run_time) AS JobStartedAt,
       h.message, h.instance_id
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j ON j.job_id = h.job_id
WHERE h.step_id = 0 AND h.run_status = 0 AND h.run_date > 0
  AND msdb.dbo.agent_datetime(h.run_date, h.run_time) >= @Since
ORDER BY JobStartedAt DESC, JobName;

A summary's message can describe several steps or the step where processing stopped. Return the individual failure message when reviewing the cause. Avoid joining all job history only by job_id and calling every combination one execution.

If a report needs execution-level grouping across all steps, define the history sequence boundaries carefully. Repeated runs of the same job have the same job_id. An unrestricted join can attach yesterday's failed step to today's successful summary.

Treat Duration as an Encoded Value

run_duration uses an HHMMSS representation rather than seconds. Hours can exceed twenty-four. Parse its parts with arithmetic before using it to calculate an approximate finish timestamp.

SELECT TOP (50) j.name AS JobName, h.step_id,
       h.run_duration,
       CONVERT(bigint, h.run_duration / 10000) * 3600
         + ((h.run_duration % 10000) / 100) * 60
         + (h.run_duration % 100) AS DurationSeconds,
       h.message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j ON j.job_id = h.job_id
WHERE h.run_status = 0 AND h.run_date > 0
ORDER BY h.instance_id DESC;

Do not cast a long duration to a clock time and discard its day component. A duration describes elapsed work, not a time of day. Keep the parsed seconds beside the original field when validating your report.

Server-local timestamps also need care around daylight-saving transitions. History does not carry a UTC offset in these integer fields. Retain the server's configured time-zone context when comparing runs across machines.

Recognize What History Cannot Show

I check retention before interpreting a quiet overnight result. Agent history limits and cleanup can trim old rows. A busy instance can lose useful records sooner than a person expects from the calendar alone.

Which job should have run but never started? History has no completed execution row for work that never began. Compare expected schedules with current job activity and the Agent service state through a separate check.

A morning report should preserve job name, step, message, and collection boundary. The job scheduler has a memory limit too. Save relevant failure evidence before it is trimmed, then investigate the cause through the step's actual command and dependencies.

Preserve Failed Agent Jobs Before Retention Removes Them

Save the selected history rows with their job identifiers and collection timestamp. Job names can change, while job_id gives the investigation a stable reference to the job definition. Keep instance_id for each returned history row so a later review can distinguish records with similar timestamps.

The history message has a finite length. A detailed step output can therefore require its configured output file or another approved diagnostic record. Check how the step records its output before assuming the message contains every detail emitted by the command.

Do not edit msdb history tables to make the morning report cleaner. Review Agent's history limits through the established configuration process when retention is inadequate. Keep an independent operational record for important failures, because a scheduler's working history is not a permanent incident archive. Failed Agent jobs need step-level evidence as well as the job summary. Keep retained-history limits visible when reporting failed Agent jobs so missing records do not become invented successes.

Related reading on this blog: Agent Jobs Running Longer Than Usual: Finding Them in Job History and Documenting SQL Agent Jobs: Schedules, Steps and Owners in One Query.

A quiet job-history report is not proof that every schedule ran, it is evidence only about the executions Agent retained.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.