Setting Up Alerts in SQL Server

Most SQL Servers run quietly. Until they don’t. By the time someone notices an application outage or a failed backup, you’re already behind. That’s why SQL Server’s built-in alerts exist - they give you an early warning before small problems become major outages.

There are a bunch of great 3^rd party tools and community scripts available, but not every firm is going to make that investment or allow open-source code on their servers.

These alerts are Microsoft-supported, built into the product, and rely on Database Mail for notifications. Configure them once, and you’ll have a safety net that runs 24/7. But like smoke detectors, too many false alarms and you’ll start ignoring them.

Step 1: Create an Operator

An operator is just the person (or distribution list) that gets notified.

In SSMS:

• SQL Server Agent >> Operators >> (right-click) New Operator

• Fill in a name and email address (use a group if possible). 100 character limit.

T-SQL Example:

USE msdb;

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

Step 2: Define the Alert

Alerts can fire on:

• Specific errors (e.g., error 823 = disk I/O issue)

• Severity levels (e.g., all severity 20+ errors)

• Performance conditions or WMI Events

In SSMS:
SQL Server Agent >> Alerts  >> (right-click) New Alert  >> choose type and scope.

T-SQL Example:

USE msdb;

EXEC msdb.dbo.sp_add_alert  
    @name = N'Error 823 Alert',  
    @message_id = 823,  
    @severity = 0,  
    @enabled = 1,  
    @delay_between_responses = 300, -- 5 minutes 
    @include_event_description_in = 1,  
    @notification_message = N'Disk I/O error (823) detected!';

Step 3: Tie It Together

Link the alert to the operator so someone actually gets notified.

In SSMS:
Open the alert >> Response >> “Notify Operators”

T-SQL Example:

EXEC msdb.dbo.sp_add_notification  
    @alert_name = N'Error 823 Alert',  
    @operator_name = N'DBA On Call',  
    @notification_method = 1; -- Email

Step 4: Enable the Mail Profile

Emails won’t get sent without this often-overlooked step.

In SSMS:

SQL Server Agent >> (right-click) >> Properties >> Alert System >> Check “Enable Mail Profile” and pick a profile from the drop down. This required Database mail to be configured and working.

Step 5: Cut the Noise

Not every warning deserves an email at 3 a.m. Start with the essentials:

• Possible Corruption (823, 824, 825)

• Critical Job failures (Agent jobs)

• Severity 19+ errors (fatal errors, serious resource issues)

  • Severity 20 may give false positives if you are using vulnerability testing software

• HADR role changes for unexpected AG failovers

 Test and adjust. If the alerts are noisy, you won’t trust them when it matters.

The Bottom Line

Setting up alerts in SQL Server is one of the easiest wins for DBAs. They’re built in, supported by Microsoft, and once tied to Database Mail and operators, they can catch serious issues before your phone rings. Just be selective, as too much noise, and the real signals get lost, or emails get “ruled” into a folder rather than acted on.

Free Disaster Readiness Quiz

I’ll trade you an email address for an honest assessment

DR Quiz - Are you ready?

SQL tidBITs:

Here’s a quick way to see what alerts are already configured:

SELECT
    [name], last_occurrence_date, enabled
FROM
    msdb.dbo.sysalerts
ORDER BY
    [name];

Link Party:

If there’s no links, it was a dull week or I just forgot ;)

Please share with your friend that enables alerts and has Outlook file them out of sight.