Warning: Constant WP_TEMP_DIR already defined in /var/www/html/blogs/glenn/wp-config.php on line 94

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/glenn/wp-config.php:94) in /var/www/html/blogs/glenn/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/glenn/wp-config.php:94) in /var/www/html/blogs/glenn/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/glenn/wp-config.php:94) in /var/www/html/blogs/glenn/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/glenn/wp-config.php:94) in /var/www/html/blogs/glenn/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/glenn/wp-config.php:94) in /var/www/html/blogs/glenn/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/glenn/wp-config.php:94) in /var/www/html/blogs/glenn/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/glenn/wp-config.php:94) in /var/www/html/blogs/glenn/wp-includes/rest-api/class-wp-rest-server.php on line 1902

Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/blogs/glenn/wp-config.php:94) in /var/www/html/blogs/glenn/wp-includes/rest-api/class-wp-rest-server.php on line 1902
{"id":477,"date":"2012-09-27T15:33:12","date_gmt":"2012-09-27T15:33:12","guid":{"rendered":"\/blogs\/glenn\/post\/How-to-Create-SQL-Server-Agent-Alerts-for-Critical-Errors.aspx"},"modified":"2019-03-18T12:17:48","modified_gmt":"2019-03-18T19:17:48","slug":"how-to-create-sql-server-agent-alerts-for-critical-errors","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-create-sql-server-agent-alerts-for-critical-errors\/","title":{"rendered":"How to Create SQL Server Agent Alerts for Critical Errors"},"content":{"rendered":"

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<\/a>. Part One is here<\/a>, Part Two is here<\/a>, and Part Three is here<\/a>. If you read and follow my recommendations in those three articles, you will have a very good foundation to build on going forward.<\/p>\n

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<\/a>. 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.<\/p>\n

\n
\n
   1:<\/span> -- Add important SQL Agent Alerts to your instance<\/span><\/pre>\n
<\/p>\n
   2:<\/span><\/pre>\n
<\/p>\n
   3:<\/span> -- Change the Alert names and @OperatorName as needed<\/span><\/pre>\n
<\/p>\n
   4:<\/span> -- Glenn Berry<\/span><\/pre>\n
<\/p>\n
   5:<\/span> -- SQLskills<\/span><\/pre>\n
<\/p>\n
   6:<\/span> -- 9-27-2012<\/span><\/pre>\n
<\/p>\n
   7:<\/span><\/pre>\n
<\/p>\n
   8:<\/span> USE<\/span> [msdb];<\/pre>\n
<\/p>\n
   9:<\/span> GO<\/span><\/pre>\n
<\/p>\n
  10:<\/span><\/pre>\n
<\/p>\n
  11:<\/span> -- Make sure you have an Agent Operator defined<\/span><\/pre>\n
<\/p>\n
  12:<\/span> -- Change @OperatorName as needed<\/span><\/pre>\n
<\/p>\n
  13:<\/span> DECLARE<\/span> @OperatorName sysname = N'SQLDBAs'<\/span>;<\/pre>\n
<\/p>\n
  14:<\/span><\/pre>\n
<\/p>\n
  15:<\/span> -- Change @CategoryName as needed<\/span><\/pre>\n
<\/p>\n
  16:<\/span> DECLARE<\/span> @CategoryName sysname = N'SQL Server Agent Alerts'<\/span>;<\/pre>\n
<\/p>\n
  17:<\/span><\/pre>\n
<\/p>\n
  18:<\/span> -- Add Alert Category if it does not exist<\/span><\/pre>\n
<\/p>\n
  19:<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> (SELECT<\/span> *<\/pre>\n
<\/p>\n
  20:<\/span>                FROM<\/span> msdb.dbo.syscategories<\/pre>\n
<\/p>\n
  21:<\/span>                WHERE<\/span> category_class = 2  -- ALERT<\/span><\/pre>\n
<\/p>\n
  22:<\/span>                AND<\/span> category_type = 3<\/pre>\n
<\/p>\n
  23:<\/span>                AND<\/span> name = @CategoryName)<\/pre>\n
<\/p>\n
  24:<\/span>     BEGIN<\/span><\/pre>\n
<\/p>\n
  25:<\/span>         EXEC<\/span> dbo.sp_add_category @class<\/span> = N'ALERT'<\/span>, @type = N'NONE'<\/span>, @name = @CategoryName;<\/pre>\n
<\/p>\n
  26:<\/span>     END<\/span><\/pre>\n
<\/p>\n
  27:<\/span><\/pre>\n
<\/p>\n
  28:<\/span> -- Get the server name<\/span><\/pre>\n
<\/p>\n
  29:<\/span> DECLARE<\/span> @ServerName sysname = (SELECT<\/span> @@SERVERNAME);<\/pre>\n
<\/p>\n
  30:<\/span><\/pre>\n
<\/p>\n
  31:<\/span><\/pre>\n
<\/p>\n
  32:<\/span> -- Alert Names start with the name of the server <\/span><\/pre>\n
<\/p>\n
  33:<\/span> DECLARE<\/span> @Sev19AlertName sysname = @ServerName + N' Alert - Sev 19 Error: Fatal Error in Resource'<\/span>;<\/pre>\n
<\/p>\n
  34:<\/span> DECLARE<\/span> @Sev20AlertName sysname = @ServerName + N' Alert - Sev 20 Error: Fatal Error in Current Process'<\/span>;<\/pre>\n
<\/p>\n
  35:<\/span> DECLARE<\/span> @Sev21AlertName sysname = @ServerName + N' Alert - Sev 21 Error: Fatal Error in Database Process'<\/span>;<\/pre>\n
<\/p>\n
  36:<\/span> DECLARE<\/span> @Sev22AlertName sysname = @ServerName + N' Alert - Sev 22 Error Fatal Error: Table Integrity Suspect'<\/span>;<\/pre>\n
<\/p>\n
  37:<\/span> DECLARE<\/span> @Sev23AlertName sysname = @ServerName + N' Alert - Sev 23 Error: Fatal Error Database Integrity Suspect'<\/span>;<\/pre>\n
<\/p>\n
  38:<\/span> DECLARE<\/span> @Sev24AlertName sysname = @ServerName + N' Alert - Sev 24 Error: Fatal Hardware Error'<\/span>;<\/pre>\n
<\/p>\n
  39:<\/span> DECLARE<\/span> @Sev25AlertName sysname = @ServerName + N' Alert - Sev 25 Error: Fatal Error'<\/span>;<\/pre>\n
<\/p>\n
  40:<\/span> DECLARE<\/span> @Error825AlertName sysname = @ServerName + N' Alert - Error 825: Read-Retry Required'<\/span>;<\/pre>\n
<\/p>\n
  41:<\/span><\/pre>\n
<\/p>\n
  42:<\/span><\/pre>\n
<\/p>\n
  43:<\/span><\/pre>\n
<\/p>\n
  44:<\/span> -- Sev 19 Error: Fatal Error in Resource<\/span><\/pre>\n
<\/p>\n
  45:<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> (SELECT<\/span> name FROM<\/span> msdb.dbo.sysalerts WHERE<\/span> name = @Sev19AlertName)<\/pre>\n
<\/p>\n
  46:<\/span>     EXEC<\/span> msdb.dbo.sp_add_alert @name = @Sev19AlertName,<\/pre>\n
<\/p>\n
  47:<\/span>                   @message_id=0, @severity=19, @enabled=1,<\/pre>\n
<\/p>\n
  48:<\/span>                   @delay_between_responses=900, @include_event_description_in=1,<\/pre>\n
<\/p>\n
  49:<\/span>                   @category_name = @CategoryName,<\/pre>\n
<\/p>\n
  50:<\/span>                   @job_id=N'00000000-0000-0000-0000-000000000000'<\/span>;<\/pre>\n
<\/p>\n
  51:<\/span><\/pre>\n
<\/p>\n
  52:<\/span> -- Add a notification if it does not exist<\/span><\/pre>\n
<\/p>\n
  53:<\/span> IF<\/span> NOT<\/span> EXISTS<\/span>(SELECT<\/span> *<\/pre>\n
<\/p>\n
  54:<\/span>           FROM<\/span> dbo.sysalerts AS<\/span> sa<\/pre>\n
<\/p>\n
  55:<\/span>           INNER<\/span> JOIN<\/span> dbo.sysnotifications AS<\/span> sn<\/pre>\n
<\/p>\n
  56:<\/span>           ON<\/span> sa.id = sn.alert_id<\/pre>\n
<\/p>\n
  57:<\/span>           WHERE<\/span> sa.name = @Sev19AlertName)<\/pre>\n
<\/p>\n
  58:<\/span>     BEGIN<\/span><\/pre>\n
<\/p>\n
  59:<\/span>         EXEC<\/span> msdb.dbo.sp_add_notification @alert_name = @Sev19AlertName, @operator_name=@OperatorName, @notification_method = 1;<\/pre>\n
<\/p>\n
  60:<\/span>     END<\/span><\/pre>\n
<\/p>\n
  61:<\/span><\/pre>\n
<\/p>\n
  62:<\/span><\/pre>\n
<\/p>\n
  63:<\/span><\/pre>\n
<\/p>\n
  64:<\/span><\/pre>\n
<\/p>\n
  65:<\/span><\/pre>\n
<\/p>\n
  66:<\/span> -- Sev 20 Error: Fatal Error in Current Process<\/span><\/pre>\n
<\/p>\n
  67:<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> (SELECT<\/span> name FROM<\/span> msdb.dbo.sysalerts WHERE<\/span> name = @Sev20AlertName)<\/pre>\n
<\/p>\n
  68:<\/span>     EXEC<\/span> msdb.dbo.sp_add_alert @name = @Sev20AlertName,<\/pre>\n
<\/p>\n
  69:<\/span>                   @message_id=0, @severity=20, @enabled=1,<\/pre>\n
<\/p>\n
  70:<\/span>                   @delay_between_responses=900, @include_event_description_in=1,<\/pre>\n
<\/p>\n
  71:<\/span>                   @category_name = @CategoryName,<\/pre>\n
<\/p>\n
  72:<\/span>                   @job_id=N'00000000-0000-0000-0000-000000000000'<\/span><\/pre>\n
<\/p>\n
  73:<\/span><\/pre>\n
<\/p>\n
  74:<\/span> -- Add a notification if it does not exist<\/span><\/pre>\n
<\/p>\n
  75:<\/span> IF<\/span> NOT<\/span> EXISTS<\/span>(SELECT<\/span> *<\/pre>\n
<\/p>\n
  76:<\/span>           FROM<\/span> dbo.sysalerts AS<\/span> sa<\/pre>\n
<\/p>\n
  77:<\/span>           INNER<\/span> JOIN<\/span> dbo.sysnotifications AS<\/span> sn<\/pre>\n
<\/p>\n
  78:<\/span>           ON<\/span> sa.id = sn.alert_id<\/pre>\n
<\/p>\n
  79:<\/span>           WHERE<\/span> sa.name = @Sev20AlertName)<\/pre>\n
<\/p>\n
  80:<\/span>     BEGIN<\/span><\/pre>\n
<\/p>\n
  81:<\/span>         EXEC<\/span> msdb.dbo.sp_add_notification @alert_name = @Sev20AlertName, @operator_name=@OperatorName, @notification_method = 1;<\/pre>\n
<\/p>\n
  82:<\/span>     END<\/span><\/pre>\n
<\/p>\n
  83:<\/span><\/pre>\n
<\/p>\n
  84:<\/span><\/pre>\n
<\/p>\n
  85:<\/span><\/pre>\n
<\/p>\n
  86:<\/span> -- Sev 21 Error: Fatal Error in Database Process<\/span><\/pre>\n
<\/p>\n
  87:<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> (SELECT<\/span> name FROM<\/span> msdb.dbo.sysalerts WHERE<\/span> name = @Sev21AlertName)<\/pre>\n
<\/p>\n
  88:<\/span>     EXEC<\/span> msdb.dbo.sp_add_alert @name = @Sev21AlertName,<\/pre>\n
<\/p>\n
  89:<\/span>                   @message_id=0, @severity=21, @enabled=1,<\/pre>\n
<\/p>\n
  90:<\/span>                   @delay_between_responses=900, @include_event_description_in=1,<\/pre>\n
<\/p>\n
  91:<\/span>                   @category_name = @CategoryName,<\/pre>\n
<\/p>\n
  92:<\/span>                   @job_id=N'00000000-0000-0000-0000-000000000000'<\/span>;<\/pre>\n
<\/p>\n
  93:<\/span><\/pre>\n
<\/p>\n
  94:<\/span> -- Add a notification if it does not exist<\/span><\/pre>\n
<\/p>\n
  95:<\/span> IF<\/span> NOT<\/span> EXISTS<\/span>(SELECT<\/span> *<\/pre>\n
<\/p>\n
  96:<\/span>           FROM<\/span> dbo.sysalerts AS<\/span> sa<\/pre>\n
<\/p>\n
  97:<\/span>           INNER<\/span> JOIN<\/span> dbo.sysnotifications AS<\/span> sn<\/pre>\n
<\/p>\n
  98:<\/span>           ON<\/span> sa.id = sn.alert_id<\/pre>\n
<\/p>\n
  99:<\/span>           WHERE<\/span> sa.name = @Sev21AlertName)<\/pre>\n
<\/p>\n
 100:<\/span>     BEGIN<\/span><\/pre>\n
<\/p>\n
 101:<\/span>         EXEC<\/span> msdb.dbo.sp_add_notification @alert_name = @Sev21AlertName, @operator_name=@OperatorName, @notification_method = 1;<\/pre>\n
<\/p>\n
 102:<\/span>     END<\/span><\/pre>\n
<\/p>\n
 103:<\/span><\/pre>\n
<\/p>\n
 104:<\/span><\/pre>\n
<\/p>\n
 105:<\/span><\/pre>\n
<\/p>\n
 106:<\/span> -- Sev 22 Error: Fatal Error Table Integrity Suspect<\/span><\/pre>\n
<\/p>\n
 107:<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> (SELECT<\/span> name FROM<\/span> msdb.dbo.sysalerts WHERE<\/span> name = @Sev22AlertName)<\/pre>\n
<\/p>\n
 108:<\/span>     EXEC<\/span> msdb.dbo.sp_add_alert @name = @Sev22AlertName,<\/pre>\n
<\/p>\n
 109:<\/span>                   @message_id=0, @severity=22, @enabled=1,<\/pre>\n
<\/p>\n
 110:<\/span>                   @delay_between_responses=900, @include_event_description_in=1,<\/pre>\n
<\/p>\n
 111:<\/span>                   @category_name = @CategoryName,<\/pre>\n
<\/p>\n
 112:<\/span>                   @job_id=N'00000000-0000-0000-0000-000000000000'<\/span>;<\/pre>\n
<\/p>\n
 113:<\/span><\/pre>\n
<\/p>\n
 114:<\/span> -- Add a notification if it does not exist<\/span><\/pre>\n
<\/p>\n
 115:<\/span> IF<\/span> NOT<\/span> EXISTS<\/span>(SELECT<\/span> *<\/pre>\n
<\/p>\n
 116:<\/span>           FROM<\/span> dbo.sysalerts AS<\/span> sa<\/pre>\n
<\/p>\n
 117:<\/span>           INNER<\/span> JOIN<\/span> dbo.sysnotifications AS<\/span> sn<\/pre>\n
<\/p>\n
 118:<\/span>           ON<\/span> sa.id = sn.alert_id<\/pre>\n
<\/p>\n
 119:<\/span>           WHERE<\/span> sa.name = @Sev22AlertName)<\/pre>\n
<\/p>\n
 120:<\/span>     BEGIN<\/span><\/pre>\n
<\/p>\n
 121:<\/span>         EXEC<\/span> msdb.dbo.sp_add_notification @alert_name = @Sev22AlertName, @operator_name=@OperatorName, @notification_method = 1;<\/pre>\n
<\/p>\n
 122:<\/span>     END<\/span><\/pre>\n
<\/p>\n
 123:<\/span><\/pre>\n
<\/p>\n
 124:<\/span><\/pre>\n
<\/p>\n
 125:<\/span> -- Sev 23 Error: Fatal Error Database Integrity Suspect<\/span><\/pre>\n
<\/p>\n
 126:<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> (SELECT<\/span> name FROM<\/span> msdb.dbo.sysalerts WHERE<\/span> name = @Sev23AlertName)<\/pre>\n
<\/p>\n
 127:<\/span>     EXEC<\/span> msdb.dbo.sp_add_alert @name = @Sev23AlertName,<\/pre>\n
<\/p>\n
 128:<\/span>                   @message_id=0, @severity=23, @enabled=1,<\/pre>\n
<\/p>\n
 129:<\/span>                   @delay_between_responses=900, @include_event_description_in=1,<\/pre>\n
<\/p>\n
 130:<\/span>                   @category_name = @CategoryName,<\/pre>\n
<\/p>\n
 131:<\/span>                   @job_id=N'00000000-0000-0000-0000-000000000000'<\/span>;<\/pre>\n
<\/p>\n
 132:<\/span><\/pre>\n
<\/p>\n
 133:<\/span> -- Add a notification if it does not exist<\/span><\/pre>\n
<\/p>\n
 134:<\/span> IF<\/span> NOT<\/span> EXISTS<\/span>(SELECT<\/span> *<\/pre>\n
<\/p>\n
 135:<\/span>           FROM<\/span> dbo.sysalerts AS<\/span> sa<\/pre>\n
<\/p>\n
 136:<\/span>           INNER<\/span> JOIN<\/span> dbo.sysnotifications AS<\/span> sn<\/pre>\n
<\/p>\n
 137:<\/span>           ON<\/span> sa.id = sn.alert_id<\/pre>\n
<\/p>\n
 138:<\/span>           WHERE<\/span> sa.name = @Sev23AlertName)<\/pre>\n
<\/p>\n
 139:<\/span>     BEGIN<\/span><\/pre>\n
<\/p>\n
 140:<\/span>         EXEC<\/span> msdb.dbo.sp_add_notification @alert_name = @Sev23AlertName, @operator_name = @OperatorName, @notification_method = 1;<\/pre>\n
<\/p>\n
 141:<\/span>     END<\/span><\/pre>\n
<\/p>\n
 142:<\/span><\/pre>\n
<\/p>\n
 143:<\/span><\/pre>\n
<\/p>\n
 144:<\/span> -- Sev 24 Error: Fatal Hardware Error<\/span><\/pre>\n
<\/p>\n
 145:<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> (SELECT<\/span> name FROM<\/span> msdb.dbo.sysalerts WHERE<\/span> name = @Sev24AlertName)<\/pre>\n
<\/p>\n
 146:<\/span>     EXEC<\/span> msdb.dbo.sp_add_alert @name = @Sev24AlertName,<\/pre>\n
<\/p>\n
 147:<\/span>                   @message_id=0, @severity=24, @enabled=1,<\/pre>\n
<\/p>\n
 148:<\/span>                   @delay_between_responses=900, @include_event_description_in=1,<\/pre>\n
<\/p>\n
 149:<\/span>                   @category_name = @CategoryName,<\/pre>\n
<\/p>\n
 150:<\/span>                   @job_id=N'00000000-0000-0000-0000-000000000000'<\/span>;<\/pre>\n
<\/p>\n
 151:<\/span><\/pre>\n
<\/p>\n
 152:<\/span> -- Add a notification if it does not exist<\/span><\/pre>\n
<\/p>\n
 153:<\/span> IF<\/span> NOT<\/span> EXISTS<\/span>(SELECT<\/span> *<\/pre>\n
<\/p>\n
 154:<\/span>           FROM<\/span> dbo.sysalerts AS<\/span> sa<\/pre>\n
<\/p>\n
 155:<\/span>           INNER<\/span> JOIN<\/span> dbo.sysnotifications AS<\/span> sn<\/pre>\n
<\/p>\n
 156:<\/span>           ON<\/span> sa.id = sn.alert_id<\/pre>\n
<\/p>\n
 157:<\/span>           WHERE<\/span> sa.name = @Sev24AlertName)<\/pre>\n
<\/p>\n
 158:<\/span>     BEGIN<\/span><\/pre>\n
<\/p>\n
 159:<\/span>         EXEC<\/span> msdb.dbo.sp_add_notification @alert_name = @Sev24AlertName, @operator_name = @OperatorName, @notification_method = 1;<\/pre>\n
<\/p>\n
 160:<\/span>     END<\/span><\/pre>\n
<\/p>\n
 161:<\/span><\/pre>\n
<\/p>\n
 162:<\/span><\/pre>\n
<\/p>\n
 163:<\/span> -- Sev 25 Error: Fatal Error<\/span><\/pre>\n
<\/p>\n
 164:<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> (SELECT<\/span> name FROM<\/span> msdb.dbo.sysalerts WHERE<\/span> name = @Sev25AlertName)<\/pre>\n
<\/p>\n
 165:<\/span>     EXEC<\/span> msdb.dbo.sp_add_alert @name = @Sev25AlertName,<\/pre>\n
<\/p>\n
 166:<\/span>                   @message_id=0, @severity=25, @enabled=1,<\/pre>\n
<\/p>\n
 167:<\/span>                   @delay_between_responses=900, @include_event_description_in=1,<\/pre>\n
<\/p>\n
 168:<\/span>                   @category_name = @CategoryName,<\/pre>\n
<\/p>\n
 169:<\/span>                   @job_id=N'00000000-0000-0000-0000-000000000000'<\/span>;<\/pre>\n
<\/p>\n
 170:<\/span><\/pre>\n
<\/p>\n
 171:<\/span> -- Add a notification if it does not exist<\/span><\/pre>\n
<\/p>\n
 172:<\/span> IF<\/span> NOT<\/span> EXISTS<\/span>(SELECT<\/span> *<\/pre>\n
<\/p>\n
 173:<\/span>           FROM<\/span> dbo.sysalerts AS<\/span> sa<\/pre>\n
<\/p>\n
 174:<\/span>           INNER<\/span> JOIN<\/span> dbo.sysnotifications AS<\/span> sn<\/pre>\n
<\/p>\n
 175:<\/span>           ON<\/span> sa.id = sn.alert_id<\/pre>\n
<\/p>\n
 176:<\/span>           WHERE<\/span> sa.name = @Sev25AlertName)<\/pre>\n
<\/p>\n
 177:<\/span>     BEGIN<\/span><\/pre>\n
<\/p>\n
 178:<\/span>         EXEC<\/span> msdb.dbo.sp_add_notification @alert_name = @Sev25AlertName, @operator_name = @OperatorName, @notification_method = 1;<\/pre>\n
<\/p>\n
 179:<\/span>     END<\/span><\/pre>\n
<\/p>\n
 180:<\/span><\/pre>\n
<\/p>\n
 181:<\/span><\/pre>\n
<\/p>\n
 182:<\/span><\/pre>\n
<\/p>\n
 183:<\/span> -- Error 825: Read-Retry Required<\/span><\/pre>\n
<\/p>\n
 184:<\/span> IF<\/span> NOT<\/span> EXISTS<\/span> (SELECT<\/span> name FROM<\/span> msdb.dbo.sysalerts WHERE<\/span> name = @Error825AlertName)<\/pre>\n
<\/p>\n
 185:<\/span>     EXEC<\/span> msdb.dbo.sp_add_alert @name = @Error825AlertName,<\/pre>\n
<\/p>\n
 186:<\/span>                   @message_id=825, @severity=0, @enabled=1,<\/pre>\n
<\/p>\n
 187:<\/span>                   @delay_between_responses=900, @include_event_description_in=1,<\/pre>\n
<\/p>\n
 188:<\/span>                   @category_name = @CategoryName,<\/pre>\n
<\/p>\n
 189:<\/span>                   @job_id=N'00000000-0000-0000-0000-000000000000'<\/span>;<\/pre>\n
<\/p>\n
 190:<\/span><\/pre>\n
<\/p>\n
 191:<\/span><\/pre>\n
<\/p>\n
 192:<\/span> -- Add a notification if it does not exist<\/span><\/pre>\n
<\/p>\n
 193:<\/span> IF<\/span> NOT<\/span> EXISTS<\/span>(SELECT<\/span> *<\/pre>\n
<\/p>\n
 194:<\/span>           FROM<\/span> dbo.sysalerts AS<\/span> sa<\/pre>\n
<\/p>\n
 195:<\/span>           INNER<\/span> JOIN<\/span> dbo.sysnotifications AS<\/span> sn<\/pre>\n
<\/p>\n
 196:<\/span>           ON<\/span> sa.id = sn.alert_id<\/pre>\n
<\/p>\n
 197:<\/span>           WHERE<\/span> sa.name = @Error825AlertName)<\/pre>\n
<\/p>\n
 198:<\/span>     BEGIN<\/span><\/pre>\n
<\/p>\n
 199:<\/span>         EXEC<\/span> msdb.dbo.sp_add_notification @alert_name = @Error825AlertName, @operator_name = @OperatorName, @notification_method = 1;<\/pre>\n
<\/p>\n
 200:<\/span>     END<\/span><\/pre>\n
<\/p>\n
 201:<\/span> GO<\/span><\/pre>\n
<\/p>\n
 202:<\/span><\/pre>\n
<\/p>\n
 203:<\/span><\/pre>\n
<\/p>\n
 204:<\/span><\/pre>\n
<\/p>\n
 205:<\/span><\/pre>\n
<\/p>\n
 206:<\/span><\/pre>\n
<\/p>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28,29,30,31,32],"tags":[324,57],"class_list":["post-477","post","type-post","status-publish","format-standard","hentry","category-sql-server-2005","category-sql-server-2008","category-sql-server-2008-r2","category-sql-server-2012","category-sql-server-agent-alerts","tag-sql-server-agent-alerts","tag-sql-server-critical-errors"],"yoast_head":"\nHow to Create SQL Server Agent Alerts for Critical Errors - Glenn Berry<\/title>\n<meta name=\"description\" content=\"Describes how to create SQL Server Agent Alerts for certain critical hardware abd database errors\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-create-sql-server-agent-alerts-for-critical-errors\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Create SQL Server Agent Alerts for Critical Errors - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"Describes how to create SQL Server Agent Alerts for certain critical hardware abd database errors\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-create-sql-server-agent-alerts-for-critical-errors\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2012-09-27T15:33:12+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-03-18T19:17:48+00:00\" \/>\n<meta name=\"author\" content=\"Glenn Berry\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Glenn Berry\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-create-sql-server-agent-alerts-for-critical-errors\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-create-sql-server-agent-alerts-for-critical-errors\/\",\"name\":\"How to Create SQL Server Agent Alerts for Critical Errors - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2012-09-27T15:33:12+00:00\",\"dateModified\":\"2019-03-18T19:17:48+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\"},\"description\":\"Describes how to create SQL Server Agent Alerts for certain critical hardware abd database errors\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-create-sql-server-agent-alerts-for-critical-errors\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-create-sql-server-agent-alerts-for-critical-errors\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-create-sql-server-agent-alerts-for-critical-errors\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Create SQL Server Agent Alerts for Critical Errors\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\",\"name\":\"Glenn Berry\",\"description\":\"Semi-random musings about SQL Server performance\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\",\"name\":\"Glenn Berry\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"caption\":\"Glenn Berry\"},\"sameAs\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to Create SQL Server Agent Alerts for Critical Errors - Glenn Berry","description":"Describes how to create SQL Server Agent Alerts for certain critical hardware abd database errors","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-create-sql-server-agent-alerts-for-critical-errors\/","og_locale":"en_US","og_type":"article","og_title":"How to Create SQL Server Agent Alerts for Critical Errors - Glenn Berry","og_description":"Describes how to create SQL Server Agent Alerts for certain critical hardware abd database errors","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-create-sql-server-agent-alerts-for-critical-errors\/","og_site_name":"Glenn Berry","article_published_time":"2012-09-27T15:33:12+00:00","article_modified_time":"2019-03-18T19:17:48+00:00","author":"Glenn Berry","twitter_misc":{"Written by":"Glenn Berry","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-create-sql-server-agent-alerts-for-critical-errors\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-create-sql-server-agent-alerts-for-critical-errors\/","name":"How to Create SQL Server Agent Alerts for Critical Errors - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2012-09-27T15:33:12+00:00","dateModified":"2019-03-18T19:17:48+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7"},"description":"Describes how to create SQL Server Agent Alerts for certain critical hardware abd database errors","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-create-sql-server-agent-alerts-for-critical-errors\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-create-sql-server-agent-alerts-for-critical-errors\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/how-to-create-sql-server-agent-alerts-for-critical-errors\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/glenn\/"},{"@type":"ListItem","position":2,"name":"How to Create SQL Server Agent Alerts for Critical Errors"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/","name":"Glenn Berry","description":"Semi-random musings about SQL Server performance","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7","name":"Glenn Berry","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","caption":"Glenn Berry"},"sameAs":["https:\/\/www.sqlskills.com\/blogs\/glenn\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/477","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/comments?post=477"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/477\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=477"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=477"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=477"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}