Which failed job would you learn about only after a user calls? Tracking job failures across servers starts with the job-level history in msdb and a daily report. Add a notification path so a missing report is itself suspicious.
Choose the Servers in Scope for Tracking Job Failures
Start with an owned list of SQL Server instances and the jobs expected on each one. A central report cannot distinguish a healthy server from a server it forgot to poll. Record instance name, environment, contact, collection time, and whether SQL Server Agent is installed and running. Include the expected job count only as a check against your own inventory, not a fixed number copied from another estate.
I ask who owns a job before building the alert. A failure without an owner tends to become yesterday’s failure. Which jobs must finish before staff arrive? Mark those as critical and set a response deadline. Keep the list under change control so a new instance or renamed job appears in the report promptly.
Read the Job Outcome Row
The sysjobhistory table in msdb stores job and step history. A row with step_id equal to zero represents the overall job outcome. Filter run_status equal to zero for failures. The query below shows recent failed job-level rows on the connected instance. It uses the stored date integer so you can audit the history without depending on a local formatting convention.
I first inspect the failed job row, then the step rows for that execution. Grouping all step rows into a failure count can count one job several times. History retention matters too. If Agent purges rows quickly, the daily report needs to collect them before they disappear.
SELECT TOP (100)
@@SERVERNAME AS InstanceName,
j.name AS JobName,
h.instance_id,
h.run_date,
h.run_time,
h.run_duration,
h.message
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
ORDER BY h.instance_id DESC;Build a Daily Window for Tracking Job Failures
A daily report needs an explicit time window and time zone. Use Agent’s run_date and run_time to select the period your team reviews, then avoid double-counting when a collection runs again. Store the last collected history instance_id per server or a reliable run boundary. Include failed and canceled outcomes separately. A job still running is not yet a successful job, so current activity needs a different check.
The next query produces failures since the start of the current server-local day. Adjust the window for your operations schedule before using it as a daily report. I run the query from each instance or through a controlled central execution path, then tag every row with its source.
SELECT
@@SERVERNAME AS InstanceName,
j.name AS JobName,
h.run_date,
h.run_time,
h.message
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 >= CONVERT(int, CONVERT(char(8), GETDATE(), 112))
ORDER BY h.instance_id DESC;Collect Without Hiding Missing Servers
A Central Management Server can run the same read-only query against registered instances from SSMS, or an approved scheduled collector can write results into a reporting store. Whichever method you use, record collection success per instance. A central page with no rows can mean no failures, a stopped Agent, an unreachable server, or a broken collector. Those states need different labels.
I make the server list visible next to the result. If one server did not answer, the report should say so in plain language. An empty grid is a terrible detective. Test the report with an intentionally unreachable test endpoint so the missing-source path is as clear as the failure path.
Find the Failed Step
After the job-level row identifies a failure, inspect step history and messages for that job near the run. Capture the step name, retry status, and command category. Protect sensitive command text in shared reports. The failure message can point to a file share, permission, or external process rather than T-SQL. Do not assume every Agent job is a query.
I keep a direct path from the daily summary to the full step history in msdb. The summary should remain small. The operator can then open the detail only for the jobs that need action. A report that pastes every step message into one email makes urgent failures harder to see.
SELECT TOP (100)
j.name AS JobName,
h.step_id,
h.step_name,
h.run_status,
h.run_date,
h.run_time,
h.message
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 IN (0, 2)
ORDER BY h.instance_id DESC;Make the Failure Hard to Miss
Configure job failure notification through SQL Server Agent operators and Database Mail for critical work. Route it to a monitored team address and test the delivery chain. The daily report for tracking job failures is a second line of defense, not the only alert. Monitor whether the report itself arrives and whether each instance was collected. A silent collector should create a separate incident, not a clean-looking day.
I choose a concise subject with instance, job, and outcome. The recipient needs a next step and an owner. Do not send a success email for every routine job if it buries the one failure message. The inbox is a tool, not a trophy shelf.
Keep Tracking Job Failures With Current History and Ownership
Agent history retention settings determine how far back msdb can answer. Set retention to support the review period and the volume of jobs, then monitor its size. A central reporting table can keep a longer history if needed, with a documented cleanup rule. Reconcile job names and owners after deployments and server moves.
I review jobs that have not run when their schedule says they should. A missing run is not present as a failure row. Compare the expected schedule with current activity and last outcome. This catches disabled jobs, stopped Agent service, and schedules left behind during migration.
Review the Report as an Operator
Run the report on a normal morning and ask whether a new DBA can tell what needs action. Show server, job, start time, outcome, owner, and last collection state. Separate failures from collection errors. Link to internal runbooks through your own reporting system rather than inventing public links in the post. Record acknowledgments so a recurring failure does not vanish into a thread.
Tracking job failures is useful when it changes behavior. I remove noise and add a check after each incident. The goal is simple: an important job failure reaches the right person before the business notices it.
Related reading on this blog: Details About SQL Jobs and Job Schedules and Query to List All Jobs with Owners.
A job failure report is not an empty grid, it is proof that every expected server was checked and every failure has an owner.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
