Find SQL Server Agent Job Failures Before Users Do

Agent job failures are easy to find before users do, if you read the history in msdb each morning. SQL Server Agent runs the backups, the index jobs and the nightly loads. When one of them fails, nobody hears about it unless you set that up, and the users find out first.

Where Agent Keeps Its Record

Agent stores everything in the msdb database. The table sysjobs lists the jobs and sysjobsteps lists their steps. The table sysjobhistory holds one row for each step that ran. It also holds one outcome row for each run of the whole job, with step_id 0. The column run_status says what happened: 0 failed, 1 succeeded, 2 retry, 3 canceled and 4 in progress.

The dates need care. The columns run_date and run_time are plain integers, such as 20261006 and 161315. The column run_duration is a number shaped like HHMMSS. The function msdb.dbo.agent_datetime joins a date and a time into one datetime in server local time. Use it, and don't cut the integers apart by hand.

The demo creates three jobs with the prefix AgentDemo. Nightly Load has two steps and fails in step 2 on purpose. Cleanup has one harmless step. Monthly Report is enabled and its schedule is switched off. Run this on a test server where SQL Server Agent is running. Express edition has no Agent. If the service is stopped, the procedures still work. They print a note that Agent can't be told about the change, and nothing runs.

USE msdb;
GO
EXEC msdb.dbo.sp_add_job @job_name = N'AgentDemo Nightly Load', @enabled = 1;
EXEC msdb.dbo.sp_add_jobstep @job_name = N'AgentDemo Nightly Load', @step_id = 1, @step_name = N'Load staging', @subsystem = N'TSQL', @command = N'SELECT 1;', @on_success_action = 3;
EXEC msdb.dbo.sp_add_jobstep @job_name = N'AgentDemo Nightly Load', @step_id = 2, @step_name = N'Check totals', @subsystem = N'TSQL', @command = N'SELECT 1 / 0;', @on_success_action = 1;
EXEC msdb.dbo.sp_add_jobserver @job_name = N'AgentDemo Nightly Load', @server_name = N'(local)';
EXEC msdb.dbo.sp_add_schedule @schedule_name = N'AgentDemo Nightly 2 AM', @freq_type = 4, @freq_interval = 1, @active_start_time = 20000;
EXEC msdb.dbo.sp_attach_schedule @job_name = N'AgentDemo Nightly Load', @schedule_name = N'AgentDemo Nightly 2 AM';
EXEC msdb.dbo.sp_add_job @job_name = N'AgentDemo Cleanup', @enabled = 1;
EXEC msdb.dbo.sp_add_jobstep @job_name = N'AgentDemo Cleanup', @step_id = 1, @step_name = N'Purge old rows', @subsystem = N'TSQL', @command = N'SELECT 1;';
EXEC msdb.dbo.sp_add_jobserver @job_name = N'AgentDemo Cleanup', @server_name = N'(local)';
EXEC msdb.dbo.sp_attach_schedule @job_name = N'AgentDemo Cleanup', @schedule_name = N'AgentDemo Nightly 2 AM';
EXEC msdb.dbo.sp_add_job @job_name = N'AgentDemo Monthly Report', @enabled = 1;
EXEC msdb.dbo.sp_add_jobstep @job_name = N'AgentDemo Monthly Report', @step_id = 1, @step_name = N'Build report', @subsystem = N'TSQL', @command = N'SELECT 1;';
EXEC msdb.dbo.sp_add_jobserver @job_name = N'AgentDemo Monthly Report', @server_name = N'(local)';
EXEC msdb.dbo.sp_add_schedule @schedule_name = N'AgentDemo Month Start', @freq_type = 16, @freq_interval = 1, @freq_recurrence_factor = 1, @active_start_time = 60000, @enabled = 0;
EXEC msdb.dbo.sp_attach_schedule @job_name = N'AgentDemo Monthly Report', @schedule_name = N'AgentDemo Month Start';

Now let Agent write the history. This script starts both jobs and waits ten seconds. Step 1 of Nightly Load succeeds, step 2 divides by zero, and the job fails on purpose. It needs a running Agent. The test instance for this post has the service stopped, so the script is shown and not run.

EXEC msdb.dbo.sp_start_job @job_name = N'AgentDemo Nightly Load';
EXEC msdb.dbo.sp_start_job @job_name = N'AgentDemo Cleanup';
WAITFOR DELAY '00:00:10';

Which Jobs Failed in the Last Day

This query lists agent job failures from the last 24 hours, one row for each failed step. It asks for step_id above 0. The step row holds the error text, while the outcome row only names the last step that ran. The integer test on run_date narrows the rows, and agent_datetime applies the exact cutoff. The first line limits the demo to its own jobs. Use N'%' to see every job on a real server. Without a running Agent, this query returns no rows. The table below is a sample.

DECLARE @Pattern nvarchar(128) = N'AgentDemo %';
SELECT j.name AS JobName, h.step_id AS StepID, h.step_name AS StepName,
       msdb.dbo.agent_datetime(h.run_date, h.run_time) AS StartedAt, h.message AS Message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j ON j.job_id = h.job_id
WHERE j.name LIKE @Pattern
  AND h.run_status = 0
  AND h.step_id > 0
  AND h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(DAY, -1, GETDATE()), 112))
  AND msdb.dbo.agent_datetime(h.run_date, h.run_time) >= DATEADD(DAY, -1, GETDATE())
ORDER BY StartedAt DESC;

Sample result. The time, the account name and the wording around the error come from your server.

Read the message first. A divide by zero error points at the code in the step, so the fix is in the T-SQL. A message about a login or a missing file points at permissions or paths. The run_status value 2 marks a retry. Add it to the filter to see steps that failed once and then worked.

Which Jobs Run Too Long

A job that still succeeds can be the first warning of agent job failures. This query takes each job's latest success and averages its earlier successes. It lists the jobs that ran more than three times slower. It converts the HHMMSS duration into seconds first. Three times is a starting point, so tune it to your jobs.

DECLARE @Pattern nvarchar(128) = N'AgentDemo %';
WITH Runs AS (
    SELECT job_id, msdb.dbo.agent_datetime(run_date, run_time) AS StartedAt,
           run_duration / 10000 * 3600 + run_duration / 100 % 100 * 60 + run_duration % 100 AS Seconds
    FROM msdb.dbo.sysjobhistory
    WHERE step_id = 0 AND run_status = 1
), Latest AS (
    SELECT job_id, StartedAt, Seconds,
           ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY StartedAt DESC) AS Recency
    FROM Runs
)
SELECT j.name AS JobName, l.StartedAt, l.Seconds AS LatestSeconds,
       CAST(AVG(1.0 * r.Seconds) AS decimal(10,1)) AS EarlierAverageSeconds
FROM Latest AS l
JOIN msdb.dbo.sysjobs AS j ON j.job_id = l.job_id
JOIN Runs AS r ON r.job_id = l.job_id AND r.StartedAt < l.StartedAt
WHERE j.name LIKE @Pattern AND l.Recency = 1 AND l.StartedAt >= DATEADD(DAY, -1, GETDATE())
GROUP BY j.name, l.StartedAt, l.Seconds
HAVING l.Seconds > 3 * AVG(1.0 * r.Seconds)
ORDER BY j.name;

Sample result from a server where Cleanup normally runs about five minutes and the latest run took 45. On the test instance the query returns no rows.

In that sample, Cleanup took 2,700 seconds against an average of 295. Nothing failed, and a morning report would still call this job healthy. A job that is still running has no outcome row yet. The table msdb.dbo.sysjobactivity shows it with a start time and no stop time.

Jobs That Never Start

A disabled schedule hides the problem. Nothing fails and nothing runs. This query lists enabled jobs that have no enabled schedule. A second column tells a job that never ran from one that ran before the schedule was switched off.

DECLARE @Pattern nvarchar(128) = N'AgentDemo %';
SELECT j.name AS JobName,
       CASE WHEN NOT EXISTS (SELECT 1 FROM msdb.dbo.sysjobschedules AS x WHERE x.job_id = j.job_id)
            THEN N'no schedule' ELSE N'schedule disabled' END AS Reason,
       CASE WHEN EXISTS (SELECT 1 FROM msdb.dbo.sysjobhistory AS h WHERE h.job_id = j.job_id)
            THEN N'has history' ELSE N'no history' END AS History
FROM msdb.dbo.sysjobs AS j
WHERE j.name LIKE @Pattern
  AND j.enabled = 1
  AND NOT EXISTS (SELECT 1
                  FROM msdb.dbo.sysjobschedules AS js
                  JOIN msdb.dbo.sysschedules AS s ON s.schedule_id = js.schedule_id
                  WHERE js.job_id = j.job_id AND s.enabled = 1)
ORDER BY j.name;

Read this list before you act on it. A job that another job or an alert starts has no schedule on purpose. Also remember that Agent trims its history. By default it keeps 1,000 rows in total and 100 for each job. On a busy server, the words no history don't prove that a job never ran.

Get Told Before You Look

Reading history is a good morning habit, and an alert is better. A job can email an operator about agent job failures as they happen. The column notify_level_email holds 0 for never, 1 for success, 2 for failure and 3 for always. This query finds the jobs that notify nobody.

DECLARE @Pattern nvarchar(128) = N'AgentDemo %';
SELECT j.name AS JobName, j.notify_level_email AS EmailLevel, j.notify_email_operator_id AS OperatorID
FROM msdb.dbo.sysjobs AS j
WHERE j.name LIKE @Pattern
  AND (j.notify_level_email = 0 OR j.notify_email_operator_id = 0)
ORDER BY j.name;

To fix a job, create an operator with msdb.dbo.sp_add_operator. Then call msdb.dbo.sp_update_job with a notify level of 2 and the operator's name. Email needs Database Mail to be set up first. Alerts add a second net for problems outside jobs. Use them for severity 19 to 25 and for errors 823, 824 and 825. The demo instance has no operator, so these steps are described and not run.

Is a Monitoring Tool Better?

You could argue that a monitoring product does all this for you. It does, as long as someone reads its screen. These queries cost nothing, take a minute, and show where Agent keeps its facts.

What to Remember

Ask msdb four questions each morning: what failed, what ran slowly, what never started, and who gets told. Filter on step_id above 0 for the error. Convert dates with agent_datetime. Treat an empty history as a question, not an answer.

When you finish testing, delete the demo jobs. The procedure removes their history and their unused schedules too.

EXEC msdb.dbo.sp_delete_job @job_name = N'AgentDemo Nightly Load', @delete_unused_schedule = 1;
EXEC msdb.dbo.sp_delete_job @job_name = N'AgentDemo Cleanup', @delete_unused_schedule = 1;
EXEC msdb.dbo.sp_delete_job @job_name = N'AgentDemo Monthly Report', @delete_unused_schedule = 1;

A failed job is not bad news, it is news you read too late.

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