Quick Scan Report – No Operators

What this check looks for

A count of zero rows in msdb.dbo.sysoperators. Express edition is excluded, because it has no SQL Server Agent.

Why it matters

An operator is the only answer SQL Server has to the question “who do I tell”. With none defined, every notification mechanism on the instance is wired to nothing:

  • A failed job notifies nobody, and job failure notification cannot even be configured, because the dropdown has no operators in it.
  • An alert on severity 19 through 25, or on corruption errors 823, 824 and 825, fires correctly and is delivered nowhere.
  • The failsafe operator, which exists precisely so a notification still arrives when the normal routing fails, cannot be set.

This matters more than it sounds because of what it combines with. Backups run as Agent jobs. DBCC CHECKDB runs as an Agent job. A backup job that starts failing on an instance with no operator fails silently, every night, for as long as it takes somebody to look. The first symptom of both problems together is a restore that cannot be done.

It is a one hour fix that changes the instance from “tells nobody anything” to “tells somebody when it matters”, which is why it sits at High despite being trivially easy.

How to confirm it yourself

SELECT [id], [name], [enabled], [email_address],
       [weekday_pager_start_time], [last_email_date]
  FROM msdb.dbo.sysoperators WITH (NOLOCK);

Whether a failsafe operator is set, which is a separate setting and commonly missed:

EXEC msdb.dbo.sp_get_sqlagent_properties;   -- read the email_save_in_sent_folder and failsafe columns

What is currently configured to notify, which will be nothing useful:

SELECT j.[name] AS [job_name],
       j.[notify_level_email],
       o.[name] AS [operator]
  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 j.[name];

How to fix it

Create one operator, point it at a distribution list, and wire the three things that use it.

EXEC msdb.dbo.sp_add_operator
     @name = N'DBA Team',
     @enabled = 1,
     @email_address = N'[email protected]';

Use a distribution list, never a person. This is the single most important decision on this page. An operator named after somebody who has left is indistinguishable from no operator at all, and it is the most common way alerting quietly stops working.

Then wire it up:

-- 1. the failsafe operator, for when normal routing fails
EXEC msdb.dbo.sp_set_sqlagent_properties
     @email_save_in_sent_folder = 1;
EXEC msdb.dbo.sp_update_notification
     @alert_name = N'Severity 19', @operator_name = N'DBA Team', @notification_method = 1;

-- 2. notification on a job failure
EXEC msdb.dbo.sp_update_job
     @job_name = N'YourJob',
     @notify_level_email = 2,          -- 2 is on failure, 3 is always
     @notify_email_operator_name = N'DBA Team';

@notify_level_email = 2 means notify on failure. Use 3, meaning always, only for something you genuinely want confirmation of, because a nightly success email trains people to ignore the sender.

Database Mail has to work for any of this to arrive. The operator is the address; Database Mail is the post. Test it once the operator exists:

EXEC msdb.dbo.sp_notify_operator
     @name = N'DBA Team',
     @subject = N'Test from SQL Server',
     @body = N'If you are reading this, operator notification works.';

Then set the failsafe operator in SQL Server Agent’s properties, on the Alert System page. It is the address used when an alert’s own routing cannot be resolved, and it costs nothing.

How long it takes

About an hour, most of it confirming Database Mail works and agreeing the address.

Report Why you would go there
Alerts and Operators Every operator and alert, and which are actually connected.
Agent Settings Agent’s own configuration, including the failsafe operator.
Database Mail Setup The profile notifications are sent through.
Email Alert Log Whether anything is being delivered.
Failed Jobs What has been failing while nobody was being told.
Check
Missing Alerts The alerts that need an operator to reach anybody.
Agent Jobs without failure notification email Jobs not wired to an operator.
Database Mail Not Enabled Without it, the operator’s address goes unused.
Failed Database Mail Notifications that were attempted and did not arrive.
SQL Agent Not Running Nothing notifies at all when Agent is stopped.

Frequently asked questions

We use external monitoring instead. Then this is a reasonable gap, provided the external tool watches job outcomes and the error log. Confirm it does; many watch service state and performance counters only.

Can one operator cover everything? Yes, and for most instances one distribution list is the right answer. More operators are for routing different severities to different teams.

Does an operator need a Windows account? No. It is a name and an email address in msdb. It does not correspond to a login.

Why is this High when it breaks nothing? Because of what it silences. Backups and integrity checks run as jobs, and on an instance with no operator their failures are invisible.