SQL Agent Operators and Notifications: Who Gets the Alert

The job failed overnight, but the person responsible never received a message. SQL Agent operators define notification destinations, not guaranteed delivery. Test the full route from an actual event to the intended inbox before relying on it.

Confirm the Mail Route before Adding Destinations

SQL Server Agent needs a configured Database Mail profile for email notifications. Enable its mail system and select the intended profile in Agent properties. Restart Agent when required for the configuration to take effect, using a planned maintenance window.

This article assumes Database Mail already works and does not repeat its setup. Test that profile independently under the intended permissions. A successful direct mail test proves one part of the route, not Agent's configuration or event mapping.

I inspect the Agent mail profile before changing operators. I also confirm that the responsible team still owns the destination address. A perfectly configured notification to an abandoned mailbox is technically impressive and operationally useless.

Use a shared operational mailbox with a documented response owner where appropriate. The operator name should explain the responsibility, rather than depending on one person's continued availability. Keep personal contact details out of public scripts and example files.

Create SQL Agent Operators with the Enabled Flag On

Run the setup on an isolated test instance with SQL Server Agent available. Replace the example address with an approved test recipient. Do not use the example address and expect a real delivery result.

USE msdb;
GO
IF EXISTS (SELECT 1 FROM dbo.sysoperators WHERE name = N'DBA On Call Test')
    THROW 51000, 'Choose an unused sample operator name.', 1;
EXEC dbo.sp_add_operator
    @name = N'DBA On Call Test',
    @enabled = 1,
    @email_address = N'dba-oncall@example.com';
GO
SELECT id, name, enabled, email_address, last_email_date, last_email_time
FROM dbo.sysoperators
WHERE name = N'DBA On Call Test';

The operator's enabled flag controls eligibility for alert notifications. Inspect it alongside its email address instead of assuming an existing name represents a working destination. Updating an address also requires a fresh delivery test.

Creating the operator does not connect it to every job or alert. Each notification relationship needs its own configuration. Permission to administer those objects depends on the Agent security model, so use an authorized test administrator for these examples.

Do not update msdb system tables directly. Use the supported procedures or SSMS configuration pages. Direct edits can bypass validation and leave a notification arrangement that looks plausible but behaves unpredictably.

Attach a Job Completion Notification

The next sample job intentionally fails through a controlled THROW. It changes no business data and has no schedule. Its failure is the event used to test job-completion notification rather than a production fault.

USE msdb;
GO
IF EXISTS (SELECT 1 FROM dbo.sysjobs WHERE name = N'Operator Notification Test')
    THROW 51001, 'Choose an unused sample job name.', 1;
EXEC dbo.sp_add_job
    @job_name = N'Operator Notification Test',
    @enabled = 0,
    @notify_level_email = 2,
    @notify_email_operator_name = N'DBA On Call Test';
EXEC dbo.sp_add_jobstep
    @job_name = N'Operator Notification Test',
    @step_name = N'Controlled failure',
    @subsystem = N'TSQL',
    @database_name = N'master',
    @command = N'THROW 51010, ''Controlled notification test.'', 1;',
    @on_success_action = 1,
    @on_fail_action = 2;
EXEC dbo.sp_add_jobserver
    @job_name = N'Operator Notification Test';
GO
-- Manual execution is supported even though scheduled execution is disabled.
EXEC dbo.sp_start_job @job_name = N'Operator Notification Test';

For email notification level, zero means never, one success, two failure, and three every completion. Choose the level matching the operational requirement. An alert on failure requires the final job outcome to remain failed after its step flow finishes.

A procedure that catches an error and returns normally can make its job appear successful. A later step that exits with success can also conceal an earlier failure. Test the actual job result rather than assuming any printed error triggers failure notification.

The job owner determines important execution permissions for T-SQL steps. Keep that identity in the test evidence. Starting the job is asynchronous, so inspect its completed history before concluding the notification route was exercised.

Link Alerts to SQL Agent Operators Explicitly

Alert notifications use a separate relationship from job completion settings. A SQL Server event alert responds to matching events written to the Windows application log. An ordinary unlogged error is insufficient merely because its number matches your alert.

The following administrative setup reserves an unused custom message number for an isolated test. The alert starts disabled so you can review its relationship before generating an event. Choose a different number if the supplied number already exists.

IF EXISTS (SELECT 1 FROM sys.messages WHERE message_id = 60005)
    THROW 51002, 'Choose an unused sample message number.', 1;
EXEC sys.sp_addmessage
    @msgnum = 60005, @severity = 16,
    @msgtext = N'Controlled operator alert test.',
    @lang = N'us_english';
GO
USE msdb;
GO
EXEC dbo.sp_add_alert
    @name = N'Controlled Operator Alert',
    @message_id = 60005,
    @severity = 0,
    @enabled = 0,
    @delay_between_responses = 60,
    @include_event_description_in = 1;
EXEC dbo.sp_add_notification
    @alert_name = N'Controlled Operator Alert',
    @operator_name = N'DBA On Call Test',
    @notification_method = 1;
GO

Notification method one selects email. The response delay limits repeated alert responses within the configured interval. It reduces message storms but does not establish an acknowledgement or resolve the underlying event.

Enable the sample alert only when the test recipient expects it. Generating a logged message requires appropriate administrative authority. This deliberate use of RAISERROR WITH LOG tests event routing rather than replacing THROW in ordinary procedure error handling.

USE msdb;
GO
EXEC dbo.sp_update_alert @name = N'Controlled Operator Alert', @enabled = 1;
RAISERROR (60005, 16, 1) WITH LOG;
GO

Configure a Fallback and Know Its Limits

In SSMS, open SQL Server Agent properties and the Alert System page. Configure an enabled fail-safe operator and select the intended notification method. Verify those settings under the same administrative process used for the main route.

The fail-safe operator provides fallback handling when normal alert notification cannot select an available operator. It does not repair a broken mail profile, stopped Agent service, or unavailable mail server. Treat shared transport failures as a separate monitoring requirement.

Test fallback behavior in an isolated environment according to the documented alert conditions. Keep the original settings and restore them after the experiment. Do not disable a production on-call destination simply to see whether the fallback reacts.

A fail-safe destination also needs someone assigned to receive and act on its messages. The configuration cannot determine whether an inbox is staffed. Document escalation and response expectations outside the operator's address field.

Verify Sending and Receipt as Separate Evidence

Inspect the operator's recorded notification values after the alert test. The dates and times are stored as integers, with zero indicating no recorded value. Treat them as notification history, not proof that someone read the email.

USE msdb;
GO
SELECT name, enabled, last_email_date, last_email_time
FROM dbo.sysoperators
WHERE name = N'DBA On Call Test';
SELECT TOP (10) h.run_date, h.run_time, h.run_status, h.message
FROM dbo.sysjobhistory AS h
JOIN dbo.sysjobs AS j ON j.job_id = h.job_id
WHERE j.name = N'Operator Notification Test' AND h.step_id = 0
ORDER BY h.instance_id DESC;
SELECT TOP (10) mailitem_id, sent_status, send_request_date, sent_date,
       recipients, subject
FROM dbo.sysmail_allitems
ORDER BY mailitem_id DESC;

Match the specific job or alert message in the mail history. Inspect Database Mail event details if sending failed, then verify receipt in the intended inbox. A queued or sent record and a confirmed recipient delivery are different observations.

I test SQL Agent operators from an actual controlled failure rather than only a direct mail command. I also repeat the test after profile or recipient changes. Who receives the next failure? Require evidence beyond the operator's existence.

For SQL Agent operators, cleanup belongs to the experiment too. Delete the dedicated sample alert, job, and unused custom message after review. Keep the operator only if its approved ownership and delivery test justify continued use.

Related reading on this blog: SQL Server Alert Management: From Chaos to Clarity and Performance Condition Alerts: Warnings Before a Log File Fills.

A configured operator is not a delivered alert, it is one destination in a tested notification route.

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