SQL Server Alerts: Which Ones to Enable and How to Set Them Up Right

The worst way to learn about a SQL Server problem is from a user. Disk errors, corruption, a full transaction log or a failing memory module usually announce themselves in the error log hours or days before they cause an outage. Nobody reads the error log at 3 a.m., though. SQL Server Agent alerts do: they turn those messages into an email or a page the moment they happen.

This guide explains how alerts work, which ones every production server should have, a ready-to-run script that creates them, the prerequisites people often miss, and how to test that alerting actually works.

How SQL Server Alerts Work

SQL Server writes many errors to the Windows application log. SQL Server Agent reads that log, compares each event with the alerts you’ve defined, and when one matches, it fires the alert: it notifies an operator by email and, optionally, runs a job. An alert can be defined on:

• A severity level, for example every error of severity 23.
• A specific error number, for example error 824.
• A performance condition, for example a database’s log usage rising above 80%.
• A WMI event, for more specialized scenarios.

Which Alerts Every Production Server Needs

Severity-based alerts

Error-number alerts

Some of the most important warnings have a severity below 19, so a severity alert won’t catch them. Add these explicitly:

• 823: the operating system reported an I/O error reading or writing a page.
• 824: a page was read but its contents are wrong (bad checksum, torn page). The most common corruption error.
• 825: a read only succeeded after retrying, an early warning that storage is failing.
• 832: a page that should not have changed was modified in memory, which usually points to memory problems.
• 855 / 856: hardware memory corruption detected (uncorrectable / recovered). These are raised on supported hardware and editions.

What to do when 823, 824 or 825 fires is explained in SQL Server Database Corruption.

Optional: Always On alerts

If you use availability groups, also alert on 1480 (an availability database is changing roles, which means a failover happened) and 35264 / 35265 (data movement to a replica was suspended / resumed). Check that these are logged on your version with the query further below.

Prerequisites People Often Miss

1. Database Mail must be configured and working. Send a test email from Database Mail first.
2. SQL Server Agent must have a mail profile enabled. In SSMS: SQL Server Agent → Properties → Alert System → Enable mail profile, then select the profile. Restart SQL Server Agent afterwards. Skipping this step is the most common reason alerts “fire” but no email arrives.
3. An operator must exist, ideally a team mailbox or distribution list rather than one person’s address.
4. Set a fail-safe operator, so a notification still goes somewhere if the regular operator can’t be reached. See Fail-Safe Operator for SQL Server Agent.

Script: Create Operator and Alerts

The script below creates the operator if it’s missing, then creates every alert listed above and adds an email notification to each. It’s safe to run more than once: existing alerts and notifications are skipped. Change the operator name and email address first.

USE [msdb];
GO
DECLARE @Operator sysname = N'DBA Team',
        @Email nvarchar(100) = N'[email protected]';

IF NOT EXISTS (SELECT 1 FROM dbo.sysoperators WHERE name = @Operator)
    EXEC dbo.sp_add_operator @name = @Operator, @enabled = 1, @email_address = @Email;

DECLARE @Alerts TABLE (alert_name sysname, severity int, message_id int);
INSERT @Alerts (alert_name, severity, message_id) VALUES
 (N'Severity 017 - Insufficient Resources',          17, 0),
 (N'Severity 018 - Nonfatal Internal Error',         18, 0),
 (N'Severity 019 - Fatal Error in Resource',         19, 0),
 (N'Severity 020 - Fatal Error in Current Process',  20, 0),
 (N'Severity 021 - Fatal Error in Database Process', 21, 0),
 (N'Severity 022 - Table Integrity Suspect',         22, 0),
 (N'Severity 023 - Database Integrity Suspect',      23, 0),
 (N'Severity 024 - Hardware Error',                  24, 0),
 (N'Severity 025 - Fatal Error',                     25, 0),
 (N'Error 823 - OS I/O Error',                        0, 823),
 (N'Error 824 - Logical Consistency I/O Error',       0, 824),
 (N'Error 825 - Read-Retry Required',                 0, 825),
 (N'Error 832 - Constant Page Has Changed',           0, 832),
 (N'Error 855 - Uncorrectable Memory Corruption',     0, 855),
 (N'Error 856 - Memory Corruption Recovered',         0, 856);

DECLARE @name sysname, @sev int, @msg int;
DECLARE c CURSOR LOCAL FAST_FORWARD FOR SELECT alert_name, severity, message_id FROM @Alerts;
OPEN c;
FETCH NEXT FROM c INTO @name, @sev, @msg;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF NOT EXISTS (SELECT 1 FROM dbo.sysalerts WHERE name = @name)
        EXEC dbo.sp_add_alert @name = @name, @severity = @sev, @message_id = @msg,
             @enabled = 1, @delay_between_responses = 900,
             @include_event_description_in = 1;

    IF NOT EXISTS (SELECT 1 FROM dbo.sysalerts AS a
                   JOIN dbo.sysnotifications AS n ON n.alert_id = a.id
                   WHERE a.name = @name)
        EXEC dbo.sp_add_notification @alert_name = @name,
             @operator_name = @Operator, @notification_method = 1;  -- 1 = email

    FETCH NEXT FROM c INTO @name, @sev, @msg;
END
CLOSE c; DEALLOCATE c;

@delay_between_responses = 900 means the same alert sends at most one email every 15 minutes, so a burst of errors doesn’t flood the mailbox. @include_event_description_in = 1 puts the actual error text in the email, which saves a trip to the server to find out what happened.

Performance Condition Alerts: Catch Problems Before the Error

Error alerts tell you something has already gone wrong. Performance condition alerts can warn you before it does. A useful example: alert when a database’s transaction log is more than 80% full, well before error 9002 stops all writes:

-- Default instance: object name is SQLServer:Databases
-- Named instance:   MSSQL$InstanceName:Databases
EXEC msdb.dbo.sp_add_alert
     @name = N'SalesDB - Log Used Above 80 Percent',
     @performance_condition = N'SQLServer:Databases|Percent Log Used|SalesDB|>|80',
     @enabled = 1, @delay_between_responses = 1800;

EXEC msdb.dbo.sp_add_notification
     @alert_name = N'SalesDB - Log Used Above 80 Percent',
     @operator_name = N'DBA Team', @notification_method = 1;

If this alert fires, find out why the log can’t be truncated; see SQL Server Transaction Log Too Big?

Test That Alerting Actually Works

An alerting setup that has never been tested is an assumption, not a safety net. The simplest test raises a logged severity 19 error, which requires sysadmin rights and WITH LOG:

RAISERROR (N'Alert test - please ignore', 19, 1) WITH LOG;

Within a minute, the operator should receive an email for the “Severity 019” alert. If nothing arrives, check in this order: the Agent mail profile setting, the Database Mail log (msdb.dbo.sysmail_event_log) and the SQL Server Agent error log. Afterwards, review which alerts have fired and how often:

SELECT name, severity, message_id, enabled, occurrence_count,
       last_occurrence_date, last_occurrence_time, has_notification
FROM msdb.dbo.sysalerts
ORDER BY occurrence_count DESC;

-- Is a given error written to the log (and therefore alertable)?
SELECT message_id, severity, is_event_logged
FROM sys.messages
WHERE language_id = 1033 AND message_id IN (823, 824, 825, 832, 855, 856, 1480, 9002, 35264, 35265);

Alerts Aren’t the Whole Monitoring Story

Alerts react to events. Some problems never raise an error until it’s too late, so cover them separately:

• Failed jobs: backups, CHECKDB and maintenance jobs should notify an operator on failure. See Keep Your Business Secure Using SQL Agent Notifications.
• Missing backups and integrity checks: check last backup times and the last successful CHECKDB every day. See How to Build a SQL Server Backup Plan and DBCC CHECKDB in SQL Server.
• Disk space, blocking and performance: these need monitoring with thresholds and trends, not just error alerts.
• The error log itself: review it regularly; see our SQL Server Error Log guide.

Frequently Asked Questions