Quick Scan Report – Failed SQL Server Agent Jobs

What this check looks for

Rows in msdb..sysjobhistory with a failed run status in the last day, joined to msdb..sysjobs for the job name. Each finding gives the time, the job, the step name and the message SQL Server recorded.

This page also covers issue 239, Database Health or Stedman Jobs failing, which is the same finding narrowed to this product’s own collection jobs.

The check is skipped on Amazon RDS.

Why it matters

What matters is not that a job failed. It is which job.

Agent is where nearly all scheduled maintenance lives, so the population of jobs on a typical instance is:

• Backups. A failure here changes your recovery point, immediately and silently. Every night it keeps failing, the gap grows.
• DBCC CHECKDB. A failure means nothing is looking for corruption, and the failure may itself be corruption, because CHECKDB failing to complete is not the same as CHECKDB finding nothing.
• Log backups. A failure stops the log being truncated, so the log grows until the drive fills. That arrives days later as an apparently unrelated storage outage.
• Index and statistics maintenance, where the consequence is gradual and least urgent.
• Application and ETL jobs, where the consequence belongs to somebody else and usually gets reported by them.

So the first question on this finding is always which of those it is, and the answer changes the urgency by an order of magnitude.

The second question is how long it has been failing. The check looks at one day, because it is a scan of current state. A job that failed once last night is different from one that has failed every night since March, and the query below tells you which you have.

How to confirm it yourself

What failed recently, with the step and the message:

SELECT j.[name]                                                 AS [job_name],
       msdb.dbo.agent_datetime(h.[run_date], h.[run_time])      AS [ran_at],
       h.[step_id],
       h.[step_name],
       h.[run_duration],
       h.[message]
  FROM msdb.dbo.sysjobhistory AS h WITH (NOLOCK)
 INNER JOIN msdb.dbo.sysjobs AS j WITH (NOLOCK) ON j.[job_id] = h.[job_id]
 WHERE h.[run_status] = 0
   AND msdb.dbo.agent_datetime(h.[run_date], h.[run_time]) > DATEADD(DAY, -7, GETDATE())
 ORDER BY [ran_at] DESC;

step_id = 0 is the job outcome; anything above it is an individual step. The step rows carry the useful message; the outcome row usually says only that the job failed.

How long each one has been failing, which is the question the finding does not answer:

SELECT j.[name]                                                       AS [job_name],
       SUM(CASE WHEN h.[run_status] = 0 THEN 1 ELSE 0 END)            AS [failures],
       SUM(CASE WHEN h.[run_status] = 1 THEN 1 ELSE 0 END)            AS [successes],
       MAX(CASE WHEN h.[run_status] = 1
                THEN msdb.dbo.agent_datetime(h.[run_date], h.[run_time]) END) AS [last_success],
       MAX(CASE WHEN h.[run_status] = 0
                THEN msdb.dbo.agent_datetime(h.[run_date], h.[run_time]) END) AS [last_failure]
  FROM msdb.dbo.sysjobhistory AS h WITH (NOLOCK)
 INNER JOIN msdb.dbo.sysjobs AS j WITH (NOLOCK) ON j.[job_id] = h.[job_id]
 WHERE h.[step_id] = 0
 GROUP BY j.[name]
HAVING SUM(CASE WHEN h.[run_status] = 0 THEN 1 ELSE 0 END) > 0
 ORDER BY [last_success];

A null last_success means the job has never worked.

How to fix it

Read the message on the step row. It is almost always specific enough to act on, and the usual causes cluster:

Then check the job owner, which is the single most common cause of a job that worked for years and suddenly does not:

SELECT j.[name], SUSER_SNAME(j.[owner_sid]) AS [owner], j.[enabled]
  FROM msdb.dbo.sysjobs AS j WITH (NOLOCK)
 ORDER BY [owner];

A null owner means the login was dropped. Set it to sa, which does not leave:

EXEC msdb.dbo.sp_update_job @job_name = N'YourJob', @owner_login_name = N'sa';

And make sure the next one reaches somebody. If this finding is the first you knew, the job has no failure notification, which is its own check on this report.

How long it takes

About an hour for a typical failure. A backup job that has been failing for weeks takes longer, because catching up the backups is the real work.

Related reports

Related checks

Frequently asked questions

The job failed once and worked since. Do I care? Less, and it is still worth a look. An intermittent failure is usually a timeout or a contended resource, and both get worse as data grows.

The job succeeded but a step failed. That is a step configured to continue on failure, which is sometimes deliberate and sometimes a mistake. The Job Step Failures report finds these, and the job outcome will never show them.

Why one day? Because this is a scan of current state. The queries above look back as far as your history retention allows.

Our history only goes back a few days. That is the Agent history limit, which defaults low. Raising it in Agent properties costs a little space in msdb and makes questions like “how long has this been failing” answerable.