Quick Scan Report – Job Without Failure Notify

What this check looks for

Enabled jobs in msdb.dbo.sysjobs whose notify_level_email is not set to notify on failure, or whose notify_email_operator_id does not resolve to an operator. One finding per job, naming the job.

The check is skipped on Amazon RDS and requires sysadmin, because reading job configuration does.

Why it matters

A job that fails silently is worse than no job at all, because it creates the belief that the work is being done.

The job was created for a reason. Somebody decided backups should run, or integrity checks, or index maintenance, or a data load. The job exists, it is enabled, it is scheduled, and it appears in every inventory as evidence that the thing is handled.

Then it starts failing. A password expired, a path moved, a drive filled, a linked server went away. And:

• Nothing is sent. The failure is written to job history.
• Job history is not a place anyone looks. It is a place you look once you already suspect something.
• The absence of failure emails reads exactly like success, which is the part that makes this dangerous rather than merely untidy.

The worst version is a backup job. Every night it fails, every night nobody hears, and the gap between what you think your recovery point is and what it actually is grows by a day at a time. The first time anybody finds out is a restore.

notify_level_email has four values, and the distinction matters:

2 is the one you want for nearly everything. 3 trains people to ignore the sender, which has the same end result as 0.

How to confirm it yourself

SELECT j.[name]                     AS [job_name],
       j.[enabled],
       CASE j.[notify_level_email]
            WHEN 0 THEN 'never'
            WHEN 1 THEN 'on success'
            WHEN 2 THEN 'on failure'
            WHEN 3 THEN 'always'
            ELSE CAST(j.[notify_level_email] AS VARCHAR(10)) END AS [notify_on],
       o.[name]                     AS [operator],
       o.[email_address]
  FROM msdb.dbo.sysjobs AS j WITH (NOLOCK)
  LEFT JOIN msdb.dbo.sysoperators AS o WITH (NOLOCK)
         ON o.[id] = j.[notify_email_operator_id]
 WHERE j.[enabled] = 1
 ORDER BY CASE WHEN o.[name] IS NULL THEN 0 ELSE 1 END, j.[name];

Then find out which of them have actually been failing while nobody was told:

SELECT j.[name]                                                    AS [job_name],
       COUNT(*)                                                    AS [failures],
       MAX(msdb.dbo.agent_datetime(h.[run_date], h.[run_time]))    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
   AND h.[run_status] = 0
 GROUP BY j.[name]
 ORDER BY [failures] DESC;

That second query is the one worth running first. A job on both lists is a problem that has already happened.

How to fix it

One statement per job:

EXEC msdb.dbo.sp_update_job
     @job_name = N'YourJob',
     @notify_level_email = 2,
     @notify_email_operator_name = N'DBA Team';

Across every enabled job that is missing it:

DECLARE @job SYSNAME;
DECLARE jobs CURSOR LOCAL FAST_FORWARD FOR
    SELECT j.[name]
      FROM msdb.dbo.sysjobs AS j WITH (NOLOCK)
      LEFT JOIN msdb.dbo.sysoperators AS o WITH (NOLOCK)
             ON o.[id] = j.[notify_email_operator_id]
     WHERE j.[enabled] = 1
       AND (o.[name] IS NULL OR j.[notify_level_email] NOT IN (2, 3));
OPEN jobs;
FETCH NEXT FROM jobs INTO @job;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC msdb.dbo.sp_update_job @job_name = @job,
         @notify_level_email = 2, @notify_email_operator_name = N'DBA Team';
    FETCH NEXT FROM jobs INTO @job;
END
CLOSE jobs; DEALLOCATE jobs;

Three things have to be true before any of it arrives, and this check only covers the third: Database Mail configured, an operator that exists, and the job wired to it. The checks on Database Mail and on operators cover the first two.

Then prove it. Set a job to fail deliberately once, or use:

EXEC msdb.dbo.sp_notify_operator @name = N'DBA Team',
     @subject = N'Test', @body = N'Notification path works.';

An alerting setup nobody has ever seen produce an email is a setup nobody knows is broken.

Consider an alert on job failure as well, which catches jobs added later that nobody remembers to wire up. A single alert on the Agent job failure condition covers the whole instance rather than one job at a time.

How long it takes

About an hour for an instance, including testing that a notification actually arrives.

Related reports

Related checks

Frequently asked questions

We watch job failures in a monitoring tool. Then this is covered, and it is worth confirming the tool watches outcomes rather than just whether Agent is running. The two are easy to confuse.

Should I set notify on success as well? Generally not. A nightly success email is ignored within a week, and after that a missing one is not noticed either.

The job notifies but nothing arrives. Then the gap is Database Mail or the operator’s address. sp_notify_operator tests the path without waiting for a failure.

What about jobs that are expected to fail sometimes? Fix the job. A job that fails routinely is training people to ignore its notifications, which silences the run where it mattered.