sqlskills-logo-2015-white.png

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:

4 thoughts on “How to Create SQL Server Agent Alerts for Critical Errors

  1. you can copy the code with the line numbers to a good editor like notepad++ or even in Management studio, then while keeping ALT key pressed, you can copy only the sql code while leaving out the line numbers. this works in VS as well.

Leave a Reply

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

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.