CONTACT US

The proper way to setup SQL Server alerts

  • No Comments

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.

By the way, this check is a part of our SQL Server Health Check service.

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'<insert your email address here>'
GO

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

Use script below (by Glenn Berry).

It 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.
  1. USE [msdb]
  2. GO 
  3. -- Replace the Agent Alert if necessary.
  4. DECLARE @OperatorName SYSNAME = N'DBA Alerts';
  5.  
  6. -- Change @CategoryName as needed
  7. DECLARE @CategoryName sysname = N'SQL Server Agent Alerts';
  8.  
  9. -- Make sure you have an Agent Operator defined that matches the name you supplied
  10. IF NOT EXISTS(SELECT * FROM msdb.dbo.sysoperators WHERE name = @OperatorName)
  11. BEGIN
  12.     RAISERROR ('There is no SQL Operator with a name of %s' , 18 , 16 , @OperatorName);
  13.     RETURN;
  14. END
  15. ELSE
  16. BEGIN
  17.     PRINT 'Operator/s are setup correctly. Alerting setup will be done next...'
  18. END
  19.  
  20. -- Add Alert Category if it does not exist
  21. IF NOT EXISTS (SELECT *
  22.                FROM msdb.dbo.syscategories
  23.                WHERE category_class = 2  -- ALERT
  24.                AND category_type = 3
  25.                AND name = @CategoryName)
  26. BEGIN
  27.     EXEC dbo.sp_add_category @class = N'ALERT', @type = N'NONE', @name = @CategoryName;
  28. END
  29.  
  30. -- Get the server name
  31. DECLARE @ServerName sysname = (SELECT @@SERVERNAME);
  32.  
  33. -- Alert Names start with the name of the server
  34. DECLARE @Sev19AlertName SYSNAME = N'SQL Alert - Sev 19 Error: Fatal Error in Resource';
  35. DECLARE @Sev20AlertName SYSNAME = N'SQL Alert - Sev 20 Error: Fatal Error in Current Process';
  36. DECLARE @Sev21AlertName SYSNAME = N'SQL Alert - Sev 21 Error: Fatal Error in Database Process';
  37. DECLARE @Sev22AlertName SYSNAME = N'SQL Alert - Sev 22 Error: Fatal Error: Table Integrity Suspect';
  38. DECLARE @Sev23AlertName SYSNAME = N'SQL Alert - Sev 23 Error: Fatal Error Database Integrity Suspect';
  39. DECLARE @Sev24AlertName SYSNAME = N'SQL Alert - Sev 24 Error: Fatal Hardware Error';
  40. DECLARE @Sev25AlertName SYSNAME = N'SQL Alert - Sev 25 Error: Fatal Error';
  41.  
  42. DECLARE @Error823AlertName SYSNAME = N'SQL Alert - Error 823: A Win read or write request has failed (hardware or driver problem)';
  43. 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)'; 
  44. DECLARE @Error825AlertName SYSNAME = N'SQL Alert - Error 825: Read-Retry Required';
  45. DECLARE @Error832AlertName SYSNAME = N'SQL Alert - Error 832: Constant page has changed';
  46. DECLARE @Error855AlertName SYSNAME = N'SQL Alert - Error 855: Uncorrectable hardware memory corruption detected';
  47. DECLARE @Error856AlertName SYSNAME = N'SQL Alert - Error 856: SQL Server has detected hardware memory corruption, but has recovered the page'; 
  48.  
  49. -- Sev 19 Error: Fatal Error in Resource
  50. IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev19AlertName)
  51.     EXEC msdb.dbo.sp_add_alert @name = @Sev19AlertName,
  52.                   @message_id = 0, @severity = 19, @enabled = 1,
  53.                   @delay_between_responses = 900, @include_event_description_in = 1,
  54.                   @category_name = @CategoryName,
  55.                   @job_id = N'00000000-0000-0000-0000-000000000000';
  56.  
  57. -- Add a notification if it does not exist
  58. IF NOT EXISTS(SELECT *
  59.               FROM dbo.sysalerts AS sa
  60.               INNER JOIN dbo.sysnotifications AS sn
  61.               ON sa.id = sn.alert_id
  62.               WHERE sa.name = @Sev19AlertName)
  63.     BEGIN
  64.         EXEC msdb.dbo.sp_add_notification @alert_name = @Sev19AlertName, @operator_name = @OperatorName, @notification_method = 1;
  65.     END 
  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. -- Sev 21 Error: Fatal Error in Database Process
  84. IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev21AlertName)
  85.     EXEC msdb.dbo.sp_add_alert @name = @Sev21AlertName,
  86.                   @message_id = 0, @severity = 21, @enabled = 1,
  87.                   @delay_between_responses = 900, @include_event_description_in = 1,
  88.                   @category_name = @CategoryName,
  89.                   @job_id = N'00000000-0000-0000-0000-000000000000';
  90.  
  91. -- Add a notification if it does not exist
  92. IF NOT EXISTS(SELECT *
  93.               FROM dbo.sysalerts AS sa
  94.               INNER JOIN dbo.sysnotifications AS sn
  95.               ON sa.id = sn.alert_id
  96.               WHERE sa.name = @Sev21AlertName)
  97.     BEGIN
  98.         EXEC msdb.dbo.sp_add_notification @alert_name = @Sev21AlertName, @operator_name = @OperatorName, @notification_method = 1;
  99.     END 
  100. -- Sev 22 Error: Fatal Error Table Integrity Suspect
  101. IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev22AlertName)
  102.     EXEC msdb.dbo.sp_add_alert @name = @Sev22AlertName,
  103.                   @message_id = 0, @severity = 22, @enabled = 1,
  104.                   @delay_between_responses = 900, @include_event_description_in = 1,
  105.                   @category_name = @CategoryName,
  106.                   @job_id = N'00000000-0000-0000-0000-000000000000';
  107.  
  108. -- Add a notification if it does not exist
  109. IF NOT EXISTS(SELECT *
  110.               FROM dbo.sysalerts AS sa
  111.               INNER JOIN dbo.sysnotifications AS sn
  112.               ON sa.id = sn.alert_id
  113.               WHERE sa.name = @Sev22AlertName)
  114.     BEGIN
  115.         EXEC msdb.dbo.sp_add_notification @alert_name = @Sev22AlertName, @operator_name = @OperatorName, @notification_method = 1;
  116.     END 
  117. -- Sev 23 Error: Fatal Error Database Integrity Suspect
  118. IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev23AlertName)
  119.     EXEC msdb.dbo.sp_add_alert @name = @Sev23AlertName,
  120.                   @message_id = 0, @severity = 23, @enabled = 1,
  121.                   @delay_between_responses = 900, @include_event_description_in = 1,
  122.                   @category_name = @CategoryName,
  123.                   @job_id = N'00000000-0000-0000-0000-000000000000';
  124.  
  125. -- Add a notification if it does not exist
  126. IF NOT EXISTS(SELECT *
  127.               FROM dbo.sysalerts AS sa
  128.               INNER JOIN dbo.sysnotifications AS sn
  129.               ON sa.id = sn.alert_id
  130.               WHERE sa.name = @Sev23AlertName)
  131.     BEGIN
  132.         EXEC msdb.dbo.sp_add_notification @alert_name = @Sev23AlertName, @operator_name = @OperatorName, @notification_method = 1;
  133.     END 
  134. -- Sev 24 Error: Fatal Hardware Error
  135. IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev24AlertName)
  136.     EXEC msdb.dbo.sp_add_alert @name = @Sev24AlertName,
  137.                   @message_id = 0, @severity = 24, @enabled = 1,
  138.                   @delay_between_responses = 900, @include_event_description_in = 1,
  139.                   @category_name = @CategoryName,
  140.                   @job_id = N'00000000-0000-0000-0000-000000000000';
  141.  
  142. -- Add a notification if it does not exist
  143. IF NOT EXISTS(SELECT *
  144.               FROM dbo.sysalerts AS sa
  145.               INNER JOIN dbo.sysnotifications AS sn
  146.               ON sa.id = sn.alert_id
  147.               WHERE sa.name = @Sev24AlertName)
  148.     BEGIN
  149.         EXEC msdb.dbo.sp_add_notification @alert_name = @Sev24AlertName, @operator_name = @OperatorName, @notification_method = 1;
  150.     END 
  151. -- Sev 25 Error: Fatal Error
  152. IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev25AlertName)
  153.     EXEC msdb.dbo.sp_add_alert @name = @Sev25AlertName,
  154.                   @message_id = 0, @severity = 25, @enabled = 1,
  155.                   @delay_between_responses = 900, @include_event_description_in = 1,
  156.                   @category_name = @CategoryName,
  157.                   @job_id = N'00000000-0000-0000-0000-000000000000';
  158.  
  159. -- Add a notification if it does not exist
  160. IF NOT EXISTS(SELECT *
  161.               FROM dbo.sysalerts AS sa
  162.               INNER JOIN dbo.sysnotifications AS sn
  163.               ON sa.id = sn.alert_id
  164.               WHERE sa.name = @Sev25AlertName)
  165.     BEGIN
  166.         EXEC msdb.dbo.sp_add_notification @alert_name = @Sev25AlertName, @operator_name = @OperatorName, @notification_method = 1;
  167.     END 
  168.  
  169. IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Error823AlertName)
  170.     EXEC msdb.dbo.sp_add_alert @name = @Error823AlertName,
  171.                   @message_id = 823, @severity = 0, @enabled = 1,
  172.                   @delay_between_responses = 900, @include_event_description_in = 1,
  173.                   @category_name = @CategoryName,
  174.                   @job_id  = N'00000000-0000-0000-0000-000000000000';
  175.  
  176. -- Add a notification if it does not exist
  177. IF NOT EXISTS(SELECT *
  178.               FROM dbo.sysalerts AS sa
  179.               INNER JOIN dbo.sysnotifications AS sn
  180.               ON sa.id = sn.alert_id
  181.               WHERE sa.name = @Error823AlertName)
  182.     BEGIN
  183.         EXEC msdb.dbo.sp_add_notification @alert_name = @Error823AlertName, @operator_name = @OperatorName, @notification_method = 1;
  184.     END
  185.  
  186.  
  187. -- Error 824: Logical consistency-based I/O error
  188. IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Error824AlertName)
  189.     EXEC msdb.dbo.sp_add_alert @name = @Error824AlertName,
  190.                   @message_id = 824, @severity = 0, @enabled = 1,
  191.                   @delay_between_responses = 900, @include_event_description_in = 1,
  192.                   @category_name = @CategoryName,
  193.                   @job_id  = N'00000000-0000-0000-0000-000000000000';
  194.  
  195.  
  196. -- Add a notification if it does not exist
  197. IF NOT EXISTS(SELECT *
  198.               FROM dbo.sysalerts AS sa
  199.               INNER JOIN dbo.sysnotifications AS sn
  200.               ON sa.id = sn.alert_id
  201.               WHERE sa.name = @Error824AlertName)
  202.     BEGIN
  203.         EXEC msdb.dbo.sp_add_notification @alert_name = @Error824AlertName, @operator_name = @OperatorName, @notification_method = 1;
  204.     END
  205.  
  206. -- Error 825: Read-Retry Required
  207. IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Error825AlertName)
  208.     EXEC msdb.dbo.sp_add_alert @name = @Error825AlertName,
  209.                   @message_id = 825, @severity = 0, @enabled = 1,
  210.                   @delay_between_responses = 900, @include_event_description_in = 1,
  211.                   @category_name = @CategoryName,
  212.                   @job_id  =N'00000000-0000-0000-0000-000000000000'; 
  213.  
  214. -- Add a notification if it does not exist
  215. IF NOT EXISTS(SELECT *
  216.               FROM dbo.sysalerts AS sa
  217.               INNER JOIN dbo.sysnotifications AS sn
  218.               ON sa.id = sn.alert_id
  219.               WHERE sa.name = @Error825AlertName)
  220.     BEGIN
  221.         EXEC msdb.dbo.sp_add_notification @alert_name = @Error825AlertName, @operator_name = @OperatorName, @notification_method = 1;
  222.     END 
  223.  
  224. -- Error 832: Constant page has changed
  225.  
  226. IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Error832AlertName)
  227.     EXEC msdb.dbo.sp_add_alert @name = @Error832AlertName,
  228.                   @message_id = 832, @severity = 0, @enabled = 1,
  229.                   @delay_between_responses = 900, @include_event_description_in = 1,
  230.                   @category_name = @CategoryName,
  231.                   @job_id  = N'00000000-0000-0000-0000-000000000000';
  232.  
  233.  -- Add a notification if it does not exist
  234. IF NOT EXISTS(SELECT *
  235.               FROM dbo.sysalerts AS sa
  236.               INNER JOIN dbo.sysnotifications AS sn
  237.               ON sa.id = sn.alert_id
  238.               WHERE sa.name = @Error832AlertName)
  239.     BEGIN
  240.         EXEC msdb.dbo.sp_add_notification @alert_name = @Error832AlertName, @operator_name = @OperatorName, @notification_method = 1;
  241.     END
  242.  
  243. -- Memory Error Correction alerts
  244. -- Check for SQL Server 2012 or greater and Enterprise Edition
  245. -- You also need Windows Server 2012 or greater, plus hardware that supports memory error correction
  246. IF LEFT(CONVERT(CHAR(2),SERVERPROPERTY('ProductVersion')), 2) &gt;= '11' AND SERVERPROPERTY('EngineEdition') = 3
  247.     BEGIN
  248.         -- Error 855: Uncorrectable hardware memory corruption detected
  249.         IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Error855AlertName)
  250.             EXEC msdb.dbo.sp_add_alert @name = @Error855AlertName,
  251.                           @message_id = 855, @severity = 0, @enabled = 1,
  252.                           @delay_between_responses = 900, @include_event_description_in = 1,
  253.                           @category_name = @CategoryName,
  254.                           @job_id  = N'00000000-0000-0000-0000-000000000000'; 
  255.  
  256.         -- Add a notification if it does not exist
  257.         IF NOT EXISTS(SELECT *
  258.                       FROM dbo.sysalerts AS sa
  259.                       INNER JOIN dbo.sysnotifications AS sn
  260.                       ON sa.id = sn.alert_id
  261.                       WHERE sa.name = @Error855AlertName)
  262.             BEGIN
  263.                 EXEC msdb.dbo.sp_add_notification @alert_name = @Error855AlertName, @operator_name = @OperatorName, @notification_method = 1;
  264.             END
  265.  
  266.         -- Error 856: SQL Server has detected hardware memory corruption, but has recovered the page
  267.         IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Error856AlertName)
  268.             EXEC msdb.dbo.sp_add_alert @name = @Error856AlertName,
  269.                           @message_id = 856, @severity = 0, @enabled = 1,
  270.                           @delay_between_responses = 900, @include_event_description_in = 1,
  271.                           @category_name = @CategoryName,
  272.                           @job_id  = N'00000000-0000-0000-0000-000000000000'; 
  273.  
  274.         -- Add a notification if it does not exist
  275.         IF NOT EXISTS(SELECT *
  276.                       FROM dbo.sysalerts AS sa
  277.                       INNER JOIN dbo.sysnotifications AS sn
  278.                       ON sa.id = sn.alert_id
  279.                       WHERE sa.name = @Error856AlertName)
  280.             BEGIN
  281.                 EXEC msdb.dbo.sp_add_notification @alert_name = @Error856AlertName, @operator_name = @OperatorName, @notification_method = 1;
  282.             END
  283.     END
  284. GO

More information

Microsoft – Alerts
Microsoft – Database Engine Error Severities
James Rhoat – SQLShack -SQL Server Setup – Database Alerts
Glenn Berry – SQL Performance – SQL Server Agent Alerts for Critical Errors

Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

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