No job fails silently: setting failure notifications on every SQL Agent job at once

#Find jobs nobody hears about

SELECT j.name, j.enabled,
       j.notify_level_email, o.name AS email_operator,
       j.notify_level_eventlog
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysoperators o ON o.id = j.notify_email_operator_id
WHERE j.notify_level_email NOT IN (2, 3)        -- 2 = on failure, 3 = on completion
   OR j.notify_email_operator_id = 0
ORDER BY j.name;

notify_level_* values: 0 never, 1 on success, 2 on failure, 3 on completion.

#Prerequisites

1. Database Mail profile works (EXEC msdb.dbo.sysmail_help_status_sp; shows STARTED).
2. Agent uses that profile (Agent properties → Alert System → Enable mail profile), and Agent was restarted afterwards.
3. An operator exists:

EXEC msdb.dbo.sp_add_operator @name = N'DBA Team', @enabled = 1, @email_address = N'dba-team@example.com';

Use a distribution list, not one person's mailbox.

#Fix all jobs

DECLARE @job sysname;
DECLARE jobs CURSOR LOCAL FAST_FORWARD FOR
  SELECT name FROM msdb.dbo.sysjobs
  WHERE notify_level_email NOT IN (2,3) OR notify_email_operator_id = 0;
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',
       @notify_level_eventlog = 2;              -- also write failures to the Application log
  FETCH NEXT FROM jobs INTO @job;
END
CLOSE jobs; DEALLOCATE jobs;

Exclude jobs owned by other teams if they have their own operator. Add a WHERE on category or name prefix.

#Event log for monitoring tools

@notify_level_eventlog = 2 writes a failure event to the Windows Application log (source SQLSERVERAGENT). SCOM, Azure Monitor Agent and most monitoring agents can pick these up, which gives you a second path if email fails.

#Keep new jobs covered

New jobs are created without notifications unless the creator remembers. Two options:

• Daily fix-up job: run the script above every morning. Simple, and it catches everything.
• Daily report: list non-compliant jobs and send them to the team, so owners fix their own jobs.

Many teams run the report for application jobs and the auto-fix for DBA-owned categories.

#Test it

Create a throwaway job with one step RAISERROR('test', 16, 1);, run it, and confirm the email arrives and the event log entry appears. Then delete the job.

If no email arrives:

SELECT TOP (20) send_request_date, sent_status, recipients, subject
FROM msdb.dbo.sysmail_allitems ORDER BY mailitem_id DESC;

SELECT TOP (20) log_date, description FROM msdb.dbo.sysmail_event_log ORDER BY log_id DESC;

No row at all means Agent didn't try. Check the Agent mail profile and restart Agent.

#Fail-safe operator

Set a fail-safe operator (Agent properties → Alert System). It is used if the named operator can't be reached, for example because it's disabled or off-duty in its pager schedule.

Tip

Failure-only notifications keep inboxes quiet. Use "on completion" only for the handful of jobs where someone actively waits for the result.

#Prevent it next time

• A job standard: every job notifies on failure.
• Weekly report of jobs that break the standard.
• Test the mail path monthly with a test job.