Creating SQL Server Agent Alerts for Critical Errors

One necessary part of being a responsible DBA is to try to configure your SQL Server instances in such a manner so they will be as reliable as possible. This begins with how you initially install and configure the operating system and SQL Server, and what you do over time to maintain and monitor your SQL Server instances. I previously wrote a three part series about how to provision a new SQL Server instance that is available on the SimpleTalk web site. Part One is here, Part Two is here, and Part Three is here. If you read and follow my recommendations in those three articles, you will have a very good foundation to build on going forward.


One subject that I touched on in Part Three of that series was how to use some T-SQL to quickly create SQL Server Agent Alerts for a number of critical error conditions. SQLskills own Paul Randal previously wrote about this. SQL Agent Alerts will fire when certain types of errors occur, and you can configure them to notify a SQL Operator when this happens. This will let you find out about these types of problems much earlier than you otherwise probably would, which is a good thing!


The code in this script is an updated version of the script that I included in my article that automatically picks up the server name and uses that as part of the alert name. It also uses variables for the operator name and for the alert category name. I have also recently added an additional alert for Error 832, and for Errors 855 and 856 (if you are running SQL Server 2012 Enterprise Edition).


SQL Server 2012 Enterprise Edition has a new feature called Memory Error Recovery. It can detect (and sometimes correct) when clean database pages have become corrupted in memory. You need to have Windows Server 2012 or newer, and you must have a processor (such as an Intel Xeon 7500 series or Intel Xeon E7 series) that supports this functionality. If you have all of this, you will see this message in your SQL Server Error Log:



Machine supports memory error recovery. SQL memory protection is enabled to recover from memory corruption.



There are some more details about this in this KB article: Using SQL Server in Windows 8 and Windows Server 2012 environments. There is a PowerPoint deck about this feature here.


In this script, the delay between responses is set to 900 seconds, which is 15 minutes. That might be a little on the long side, but I did not want the operator to get spammed with e-mail alerts every couple of minutes. You can always adjust that value to whatever you see fit. You need to supply a valid SQL Operator name for my script to work correctly.

The Accidental DBA (Day 17 of 30): Configuring Alerts for High Severity Problems

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. You can find all the other posts in this series at https://www.sqlskills.com/help/AccidentalDBA. Enjoy!

One necessary part of being a responsible, pro-active DBA is to try to configure your SQL Server instances in such a manner so they will be as reliable as possible. This begins with how you initially install and configure the operating system and SQL Server, and what you do over time to maintain and monitor your SQL Server instances. I previously wrote a three part series about how to provision a new SQL Server instance that is available on the SimpleTalk web site. Part One is here, Part Two is here, and Part Three is here. If you read and follow my recommendations in those three articles, you will have a very good installation and configuration foundation to build on going forward.

One subject that I touched on in Part Three of that series was how to use some T-SQL to quickly create SQL Server Agent Alerts for a number of critical error conditions. SQL Server Agent Alerts are often confused with SQL Server Agent job notifications, but they are actually quite different. SQL Server Agent job notifications allow you to send an e-mail or page to a SQL Server Operator when an Agent job fails, succeeds or completes. The most common usage is to send an e-mail to your SQL Server Operator (which can be an e-mail distribution list) whenever a SQL Server Agent job fails. You really should make sure each of your SQL Server Agent Jobs has a proper SQL Server Agent job notification so that someone in your organization knows whenever a SQL Server Agent job fails, so that the problem can be investigated and corrected. The Notification tab for a SQL Server Agent Job is shown in Figure 1.

image

Figure 1: SQL Server Agent Job Notification Tab

While these Agent Job notifications are important, they are not the same thing as a SQL Server Agent Alert. A SQL Server Agent Alert will trigger an action (such as notifying an Operator by sending an e-mail) whenever certain errors occur or certain other performance conditions exist (that are detected from Windows Performance Monitor objects values or Windows Management Instrumentation (WMI) queries). You can also execute a SQL Server Agent Job in response to a SQL Server Agent Alert, in addition to simply notifying an Operator. This is much better than finding out about a problem by stumbling across it in the SQL Server Error Log or finding about it when it has led to some problem that is affecting your availability or data integrity. Figure 2 shows the General tab for a SQL Server Agent Alert. Figure 3 shows the Response tab for for a SQL Server Agent Alert.

image

Figure 2: SQL Server Agent Alert General Tab

 

image

Figure 3: SQL Server Agent Alert Response Tab

Paul Randal previously wrote about the importance of creating SQL Server Agent Alerts. I wanted to make it even easier to create a set of very important SQL Server Agent Alerts by just running a relatively simple T-SQL script. The code below automatically picks up the server name and uses that as part of the alert name. It also uses variables for the operator name and for the alert category name. In this script, the delay between responses is set to 900 seconds, which is 15 minutes. That might be a little on the long side, but I did not want the operator to get spammed with e-mail alerts every couple of minutes. You can always adjust that value to whatever you see fit.

You need to make sure that you have a SQL Operator defined for your instance (and that you change the name of the operator in the script below to match your Operator name). You also need to make sure that you have Database Mail properly configured and enabled, so that you will actually get an e-mail when one of your SQL Server Agent Alerts is triggered.

-- Add important SQL Agent Alerts to your instance
-- Change the @OperatorName as needed

USE [msdb];
GO

-- Make sure you have an Agent Operator defined
-- Change @OperatorName as needed
DECLARE @OperatorName sysname = N'SQLDBAGroup';

-- Change @CategoryName as needed
DECLARE @CategoryName sysname = N'SQL Server Agent Alerts';

-- 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 = @ServerName + N' Alert - Sev 19 Error: Fatal Error in Resource';
DECLARE @Sev20AlertName sysname = @ServerName + N' Alert - Sev 20 Error: Fatal Error in Current Process';
DECLARE @Sev21AlertName sysname = @ServerName + N' Alert - Sev 21 Error: Fatal Error in Database Process';
DECLARE @Sev22AlertName sysname = @ServerName + N' Alert - Sev 22 Error Fatal Error: Table Integrity Suspect';
DECLARE @Sev23AlertName sysname = @ServerName + N' Alert - Sev 23 Error: Fatal Error Database Integrity Suspect';
DECLARE @Sev24AlertName sysname = @ServerName + N' Alert - Sev 24 Error: Fatal Hardware Error';
DECLARE @Sev25AlertName sysname = @ServerName + N' Alert - Sev 25 Error: Fatal Error';
DECLARE @Error825AlertName sysname = @ServerName + N' Alert - Error 825: Read-Retry Required';

-- 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

-- 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
GO


How to Create SQL Server Agent Alerts for Critical Errors

One necessary part of being a responsible DBA is to try to configure your SQL Server instances in such a manner so they will be as reliable as possible. This begins with how you initially install and configure the operating system and SQL Server, and what you do over time to maintain and monitor your SQL Server instances. I recently wrote a three part series about how to provision a new SQL Server instance that is available on the SimpleTalk web site. Part One is here, Part Two is here, and Part Three is here. If you read and follow my recommendations in those three articles, you will have a very good foundation to build on going forward.

One subject that I touched on in Part Three of that series was how to use some T-SQL to quickly create SQL Server Agent Alerts for a number of critical error conditions. SQLskills own Paul Randal previously wrote about this. The code below is an updated version of the script that I included in my article that automatically picks up the server name and uses that as part of the alert name. It also uses variables for the operator name and for the alert category name. In this script, the delay between responses is set to 900 seconds, which is 15 minutes. That might be a little on the long side, but I did not want the operator to get spammed with e-mail alerts every couple of minutes. You can always adjust that value to whatever you see fit.

   1: -- Add important SQL Agent Alerts to your instance

   2:

   3: -- Change the Alert names and @OperatorName as needed

   4: -- Glenn Berry

   5: -- SQLskills

   6: -- 9-27-2012

   7:

   8: USE [msdb];

   9: GO

  10:

  11: -- Make sure you have an Agent Operator defined

  12: -- Change @OperatorName as needed

  13: DECLARE @OperatorName sysname = N'SQLDBAs';

  14:

  15: -- Change @CategoryName as needed

  16: DECLARE @CategoryName sysname = N'SQL Server Agent Alerts';

  17:

  18: -- Add Alert Category if it does not exist

  19: IF NOT EXISTS (SELECT *

  20:                FROM msdb.dbo.syscategories

  21:                WHERE category_class = 2  -- ALERT

  22:                AND category_type = 3

  23:                AND name = @CategoryName)

  24:     BEGIN

  25:         EXEC dbo.sp_add_category @class = N'ALERT', @type = N'NONE', @name = @CategoryName;

  26:     END

  27:

  28: -- Get the server name

  29: DECLARE @ServerName sysname = (SELECT @@SERVERNAME);

  30:

  31:

  32: -- Alert Names start with the name of the server 

  33: DECLARE @Sev19AlertName sysname = @ServerName + N' Alert - Sev 19 Error: Fatal Error in Resource';

  34: DECLARE @Sev20AlertName sysname = @ServerName + N' Alert - Sev 20 Error: Fatal Error in Current Process';

  35: DECLARE @Sev21AlertName sysname = @ServerName + N' Alert - Sev 21 Error: Fatal Error in Database Process';

  36: DECLARE @Sev22AlertName sysname = @ServerName + N' Alert - Sev 22 Error Fatal Error: Table Integrity Suspect';

  37: DECLARE @Sev23AlertName sysname = @ServerName + N' Alert - Sev 23 Error: Fatal Error Database Integrity Suspect';

  38: DECLARE @Sev24AlertName sysname = @ServerName + N' Alert - Sev 24 Error: Fatal Hardware Error';

  39: DECLARE @Sev25AlertName sysname = @ServerName + N' Alert - Sev 25 Error: Fatal Error';

  40: DECLARE @Error825AlertName sysname = @ServerName + N' Alert - Error 825: Read-Retry Required';

  41:

  42:

  43:

  44: -- Sev 19 Error: Fatal Error in Resource

  45: IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev19AlertName)

  46:     EXEC msdb.dbo.sp_add_alert @name = @Sev19AlertName,

  47:                   @message_id=0, @severity=19, @enabled=1,

  48:                   @delay_between_responses=900, @include_event_description_in=1,

  49:                   @category_name = @CategoryName,

  50:                   @job_id=N'00000000-0000-0000-0000-000000000000';

  51:

  52: -- Add a notification if it does not exist

  53: IF NOT EXISTS(SELECT *

  54:           FROM dbo.sysalerts AS sa

  55:           INNER JOIN dbo.sysnotifications AS sn

  56:           ON sa.id = sn.alert_id

  57:           WHERE sa.name = @Sev19AlertName)

  58:     BEGIN

  59:         EXEC msdb.dbo.sp_add_notification @alert_name = @Sev19AlertName, @operator_name=@OperatorName, @notification_method = 1;

  60:     END

  61:

  62:

  63:

  64:

  65:

  66: -- Sev 20 Error: Fatal Error in Current Process

  67: IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev20AlertName)

  68:     EXEC msdb.dbo.sp_add_alert @name = @Sev20AlertName,

  69:                   @message_id=0, @severity=20, @enabled=1,

  70:                   @delay_between_responses=900, @include_event_description_in=1,

  71:                   @category_name = @CategoryName,

  72:                   @job_id=N'00000000-0000-0000-0000-000000000000'

  73:

  74: -- Add a notification if it does not exist

  75: IF NOT EXISTS(SELECT *

  76:           FROM dbo.sysalerts AS sa

  77:           INNER JOIN dbo.sysnotifications AS sn

  78:           ON sa.id = sn.alert_id

  79:           WHERE sa.name = @Sev20AlertName)

  80:     BEGIN

  81:         EXEC msdb.dbo.sp_add_notification @alert_name = @Sev20AlertName, @operator_name=@OperatorName, @notification_method = 1;

  82:     END

  83:

  84:

  85:

  86: -- Sev 21 Error: Fatal Error in Database Process

  87: IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev21AlertName)

  88:     EXEC msdb.dbo.sp_add_alert @name = @Sev21AlertName,

  89:                   @message_id=0, @severity=21, @enabled=1,

  90:                   @delay_between_responses=900, @include_event_description_in=1,

  91:                   @category_name = @CategoryName,

  92:                   @job_id=N'00000000-0000-0000-0000-000000000000';

  93:

  94: -- Add a notification if it does not exist

  95: IF NOT EXISTS(SELECT *

  96:           FROM dbo.sysalerts AS sa

  97:           INNER JOIN dbo.sysnotifications AS sn

  98:           ON sa.id = sn.alert_id

  99:           WHERE sa.name = @Sev21AlertName)

 100:     BEGIN

 101:         EXEC msdb.dbo.sp_add_notification @alert_name = @Sev21AlertName, @operator_name=@OperatorName, @notification_method = 1;

 102:     END

 103:

 104:

 105:

 106: -- Sev 22 Error: Fatal Error Table Integrity Suspect

 107: IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev22AlertName)

 108:     EXEC msdb.dbo.sp_add_alert @name = @Sev22AlertName,

 109:                   @message_id=0, @severity=22, @enabled=1,

 110:                   @delay_between_responses=900, @include_event_description_in=1,

 111:                   @category_name = @CategoryName,

 112:                   @job_id=N'00000000-0000-0000-0000-000000000000';

 113:

 114: -- Add a notification if it does not exist

 115: IF NOT EXISTS(SELECT *

 116:           FROM dbo.sysalerts AS sa

 117:           INNER JOIN dbo.sysnotifications AS sn

 118:           ON sa.id = sn.alert_id

 119:           WHERE sa.name = @Sev22AlertName)

 120:     BEGIN

 121:         EXEC msdb.dbo.sp_add_notification @alert_name = @Sev22AlertName, @operator_name=@OperatorName, @notification_method = 1;

 122:     END

 123:

 124:

 125: -- Sev 23 Error: Fatal Error Database Integrity Suspect

 126: IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev23AlertName)

 127:     EXEC msdb.dbo.sp_add_alert @name = @Sev23AlertName,

 128:                   @message_id=0, @severity=23, @enabled=1,

 129:                   @delay_between_responses=900, @include_event_description_in=1,

 130:                   @category_name = @CategoryName,

 131:                   @job_id=N'00000000-0000-0000-0000-000000000000';

 132:

 133: -- Add a notification if it does not exist

 134: IF NOT EXISTS(SELECT *

 135:           FROM dbo.sysalerts AS sa

 136:           INNER JOIN dbo.sysnotifications AS sn

 137:           ON sa.id = sn.alert_id

 138:           WHERE sa.name = @Sev23AlertName)

 139:     BEGIN

 140:         EXEC msdb.dbo.sp_add_notification @alert_name = @Sev23AlertName, @operator_name = @OperatorName, @notification_method = 1;

 141:     END

 142:

 143:

 144: -- Sev 24 Error: Fatal Hardware Error

 145: IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev24AlertName)

 146:     EXEC msdb.dbo.sp_add_alert @name = @Sev24AlertName,

 147:                   @message_id=0, @severity=24, @enabled=1,

 148:                   @delay_between_responses=900, @include_event_description_in=1,

 149:                   @category_name = @CategoryName,

 150:                   @job_id=N'00000000-0000-0000-0000-000000000000';

 151:

 152: -- Add a notification if it does not exist

 153: IF NOT EXISTS(SELECT *

 154:           FROM dbo.sysalerts AS sa

 155:           INNER JOIN dbo.sysnotifications AS sn

 156:           ON sa.id = sn.alert_id

 157:           WHERE sa.name = @Sev24AlertName)

 158:     BEGIN

 159:         EXEC msdb.dbo.sp_add_notification @alert_name = @Sev24AlertName, @operator_name = @OperatorName, @notification_method = 1;

 160:     END

 161:

 162:

 163: -- Sev 25 Error: Fatal Error

 164: IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev25AlertName)

 165:     EXEC msdb.dbo.sp_add_alert @name = @Sev25AlertName,

 166:                   @message_id=0, @severity=25, @enabled=1,

 167:                   @delay_between_responses=900, @include_event_description_in=1,

 168:                   @category_name = @CategoryName,

 169:                   @job_id=N'00000000-0000-0000-0000-000000000000';

 170:

 171: -- Add a notification if it does not exist

 172: IF NOT EXISTS(SELECT *

 173:           FROM dbo.sysalerts AS sa

 174:           INNER JOIN dbo.sysnotifications AS sn

 175:           ON sa.id = sn.alert_id

 176:           WHERE sa.name = @Sev25AlertName)

 177:     BEGIN

 178:         EXEC msdb.dbo.sp_add_notification @alert_name = @Sev25AlertName, @operator_name = @OperatorName, @notification_method = 1;

 179:     END

 180:

 181:

 182:

 183: -- Error 825: Read-Retry Required

 184: IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Error825AlertName)

 185:     EXEC msdb.dbo.sp_add_alert @name = @Error825AlertName,

 186:                   @message_id=825, @severity=0, @enabled=1,

 187:                   @delay_between_responses=900, @include_event_description_in=1,

 188:                   @category_name = @CategoryName,

 189:                   @job_id=N'00000000-0000-0000-0000-000000000000';

 190:

 191:

 192: -- Add a notification if it does not exist

 193: IF NOT EXISTS(SELECT *

 194:           FROM dbo.sysalerts AS sa

 195:           INNER JOIN dbo.sysnotifications AS sn

 196:           ON sa.id = sn.alert_id

 197:           WHERE sa.name = @Error825AlertName)

 198:     BEGIN

 199:         EXEC msdb.dbo.sp_add_notification @alert_name = @Error825AlertName, @operator_name = @OperatorName, @notification_method = 1;

 200:     END

 201: GO

 202:

 203:

 204:

 205:

 206: