The proper way to setup SQL Server alerts

Category: Reliability
Item: Missing alerts

Why should you enable SQL Server alerts?

Enabling these Alerts can prevent a lot of problems:

  1. Potential to miss critical issues.
  2. Missing out on catching problems proactively.
  3. Increased chance of downtime.

Which alerts should you include?

It’s a best practice for DBAs to enable SQL Server event alerts for Severity 17 or higher.

Getting notified at least on errors below creates an early warning system of issues that may escalate.

How to set up the SQL Server alerts?

First, create an operator (notification recipient) for use with alerts and jobs.
You can use the script below, replace name and @email_address.

USE [msdb]
GO
IF NOT EXISTS (select * from msdb.dbo.sysoperators where name = 'SQL Server Alerts')
    EXEC msdb.dbo.sp_add_operator @name=N'SQL Server Alerts',
            @enabled=1,
            @pager_days=0,
            @email_address=N'SqlServerAlerts@company.com'
GO

NOTE: This solution assumes that you have already done all the Database Mail Configuration Required (SMTP).

The below script creates SQL Server alerts for severity 19 through 25 as well as specific alerts for 823, 824,825, 832, 855 and 856 errors:

  1. Replace the Agent operator Alert, variable @OperatorName. Make sure you have an Agent Operator defined that matches the name you supplied.
  2. Change @CategoryName as need.
USE [msdb]
GO 
-- Replace the Agent Alert if necessary.
DECLARE @OperatorName SYSNAME = N'DBA Alerts';
 
-- Change @CategoryName as needed
DECLARE @CategoryName sysname = N'SQL Server Agent Alerts';
 
-- Make sure you have an Agent Operator defined that matches the name you supplied
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysoperators WHERE name = @OperatorName)
BEGIN
    RAISERROR ('There is no SQL Operator with a name of %s' , 18 , 16 , @OperatorName);
    RETURN;
END
ELSE
BEGIN
    PRINT 'Operator/s are setup correctly. Alerting setup will be done next...'
END
 
-- Add Alert Category if it does not exist
IF NOT EXISTS (SELECT *
               FROM msdb.dbo.syscategories
               WHERE category_class = 2  -- ALERT
               AND category_type = 3
               AND name = @CategoryName)
BEGIN
    EXEC dbo.sp_add_category @class = N'ALERT', @type = N'NONE', @name = @CategoryName;
END
 
-- Get the server name
DECLARE @ServerName sysname = (SELECT @@SERVERNAME);
 
-- Alert Names start with the name of the server
DECLARE @Sev19AlertName SYSNAME = N'SQL Alert - Sev 19 Error: Fatal Error in Resource';
DECLARE @Sev20AlertName SYSNAME = N'SQL Alert - Sev 20 Error: Fatal Error in Current Process';
DECLARE @Sev21AlertName SYSNAME = N'SQL Alert - Sev 21 Error: Fatal Error in Database Process';
DECLARE @Sev22AlertName SYSNAME = N'SQL Alert - Sev 22 Error: Fatal Error: Table Integrity Suspect';
DECLARE @Sev23AlertName SYSNAME = N'SQL Alert - Sev 23 Error: Fatal Error Database Integrity Suspect';
DECLARE @Sev24AlertName SYSNAME = N'SQL Alert - Sev 24 Error: Fatal Hardware Error';
DECLARE @Sev25AlertName SYSNAME = N'SQL Alert - Sev 25 Error: Fatal Error';
 
DECLARE @Error823AlertName SYSNAME = N'SQL Alert - Error 823: A Win read or write request has failed (hardware or driver problem)';
DECLARE @Error824AlertName SYSNAME = N'SQL Alert - Error 824: Read page OK, but it has a problem (indicates I/O issue: failing HDD, disk firmware problems, faulty device driver, etc)'; 
DECLARE @Error825AlertName SYSNAME = N'SQL Alert - Error 825: Read-Retry Required';
DECLARE @Error832AlertName SYSNAME = N'SQL Alert - Error 832: Constant page has changed';
DECLARE @Error855AlertName SYSNAME = N'SQL Alert - Error 855: Uncorrectable hardware memory corruption detected';
DECLARE @Error856AlertName SYSNAME = N'SQL Alert - Error 856: SQL Server has detected hardware memory corruption, but has recovered the page'; 
 
-- Sev 19 Error: Fatal Error in Resource
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev19AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Sev19AlertName,
                  @message_id = 0, @severity = 19, @enabled = 1,
                  @delay_between_responses = 900, @include_event_description_in = 1,
                  @category_name = @CategoryName,
                  @job_id = N'00000000-0000-0000-0000-000000000000';
 
-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
              FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Sev19AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Sev19AlertName, @operator_name = @OperatorName, @notification_method = 1;
    END 
-- Sev 20 Error: Fatal Error in Current Process
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev20AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Sev20AlertName,
                  @message_id = 0, @severity = 20, @enabled = 1,
                  @delay_between_responses = 900, @include_event_description_in = 1,
                  @category_name = @CategoryName,
                  @job_id = N'00000000-0000-0000-0000-000000000000'
 
-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
              FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Sev20AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Sev20AlertName, @operator_name = @OperatorName, @notification_method = 1;
    END 
-- Sev 21 Error: Fatal Error in Database Process
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev21AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Sev21AlertName,
                  @message_id = 0, @severity = 21, @enabled = 1,
                  @delay_between_responses = 900, @include_event_description_in = 1,
                  @category_name = @CategoryName,
                  @job_id = N'00000000-0000-0000-0000-000000000000';
 
-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
              FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Sev21AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Sev21AlertName, @operator_name = @OperatorName, @notification_method = 1;
    END 
-- Sev 22 Error: Fatal Error Table Integrity Suspect
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev22AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Sev22AlertName,
                  @message_id = 0, @severity = 22, @enabled = 1,
                  @delay_between_responses = 900, @include_event_description_in = 1,
                  @category_name = @CategoryName,
                  @job_id = N'00000000-0000-0000-0000-000000000000';
 
-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
              FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Sev22AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Sev22AlertName, @operator_name = @OperatorName, @notification_method = 1;
    END 
-- Sev 23 Error: Fatal Error Database Integrity Suspect
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev23AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Sev23AlertName,
                  @message_id = 0, @severity = 23, @enabled = 1,
                  @delay_between_responses = 900, @include_event_description_in = 1,
                  @category_name = @CategoryName,
                  @job_id = N'00000000-0000-0000-0000-000000000000';
 
-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
              FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Sev23AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Sev23AlertName, @operator_name = @OperatorName, @notification_method = 1;
    END 
-- Sev 24 Error: Fatal Hardware Error
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev24AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Sev24AlertName,
                  @message_id = 0, @severity = 24, @enabled = 1,
                  @delay_between_responses = 900, @include_event_description_in = 1,
                  @category_name = @CategoryName,
                  @job_id = N'00000000-0000-0000-0000-000000000000';
 
-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
              FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Sev24AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Sev24AlertName, @operator_name = @OperatorName, @notification_method = 1;
    END 
-- Sev 25 Error: Fatal Error
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev25AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Sev25AlertName,
                  @message_id = 0, @severity = 25, @enabled = 1,
                  @delay_between_responses = 900, @include_event_description_in = 1,
                  @category_name = @CategoryName,
                  @job_id = N'00000000-0000-0000-0000-000000000000';
 
-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
              FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Sev25AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Sev25AlertName, @operator_name = @OperatorName, @notification_method = 1;
    END 
 
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Error823AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Error823AlertName,
                  @message_id = 823, @severity = 0, @enabled = 1,
                  @delay_between_responses = 900, @include_event_description_in = 1,
                  @category_name = @CategoryName,
                  @job_id  = N'00000000-0000-0000-0000-000000000000';
 
-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
              FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Error823AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Error823AlertName, @operator_name = @OperatorName, @notification_method = 1;
    END
 
 
-- Error 824: Logical consistency-based I/O error
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Error824AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Error824AlertName,
                  @message_id = 824, @severity = 0, @enabled = 1,
                  @delay_between_responses = 900, @include_event_description_in = 1,
                  @category_name = @CategoryName,
                  @job_id  = N'00000000-0000-0000-0000-000000000000';
 
 
-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
              FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Error824AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Error824AlertName, @operator_name = @OperatorName, @notification_method = 1;
    END
 
-- Error 825: Read-Retry Required
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Error825AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Error825AlertName,
                  @message_id = 825, @severity = 0, @enabled = 1,
                  @delay_between_responses = 900, @include_event_description_in = 1,
                  @category_name = @CategoryName,
                  @job_id  =N'00000000-0000-0000-0000-000000000000'; 
 
-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
              FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Error825AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Error825AlertName, @operator_name = @OperatorName, @notification_method = 1;
    END 
 
-- Error 832: Constant page has changed
 
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Error832AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Error832AlertName,
                  @message_id = 832, @severity = 0, @enabled = 1,
                  @delay_between_responses = 900, @include_event_description_in = 1,
                  @category_name = @CategoryName,
                  @job_id  = N'00000000-0000-0000-0000-000000000000';
 
 -- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
              FROM dbo.sysalerts AS sa
              INNER JOIN dbo.sysnotifications AS sn
              ON sa.id = sn.alert_id
              WHERE sa.name = @Error832AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Error832AlertName, @operator_name = @OperatorName, @notification_method = 1;
    END
 
-- Memory Error Correction alerts
-- Check for SQL Server 2012 or greater and Enterprise Edition
-- You also need Windows Server 2012 or greater, plus hardware that supports memory error correction
IF LEFT(CONVERT(CHAR(2),SERVERPROPERTY('ProductVersion')), 2) >= '11' AND SERVERPROPERTY('EngineEdition') = 3
    BEGIN
        -- Error 855: Uncorrectable hardware memory corruption detected
        IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Error855AlertName)
            EXEC msdb.dbo.sp_add_alert @name = @Error855AlertName,
                          @message_id = 855, @severity = 0, @enabled = 1,
                          @delay_between_responses = 900, @include_event_description_in = 1,
                          @category_name = @CategoryName,
                          @job_id  = N'00000000-0000-0000-0000-000000000000'; 
 
        -- Add a notification if it does not exist
        IF NOT EXISTS(SELECT *
                      FROM dbo.sysalerts AS sa
                      INNER JOIN dbo.sysnotifications AS sn
                      ON sa.id = sn.alert_id
                      WHERE sa.name = @Error855AlertName)
            BEGIN
                EXEC msdb.dbo.sp_add_notification @alert_name = @Error855AlertName, @operator_name = @OperatorName, @notification_method = 1;
            END
 
        -- Error 856: SQL Server has detected hardware memory corruption, but has recovered the page
        IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Error856AlertName)
            EXEC msdb.dbo.sp_add_alert @name = @Error856AlertName,
                          @message_id = 856, @severity = 0, @enabled = 1,
                          @delay_between_responses = 900, @include_event_description_in = 1,
                          @category_name = @CategoryName,
                          @job_id  = N'00000000-0000-0000-0000-000000000000'; 
 
        -- Add a notification if it does not exist
        IF NOT EXISTS(SELECT *
                      FROM dbo.sysalerts AS sa
                      INNER JOIN dbo.sysnotifications AS sn
                      ON sa.id = sn.alert_id
                      WHERE sa.name = @Error856AlertName)
            BEGIN
                EXEC msdb.dbo.sp_add_notification @alert_name = @Error856AlertName, @operator_name = @OperatorName, @notification_method = 1;
            END
    END
GO

More information

Microsoft – Alerts
Microsoft – Database Engine Error Severities
James Rhoat – SQLShack -SQL Server Setup – Database Alerts

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

Your email address will not be published. Required fields are marked *

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.