Problem
You need to configure alerts to notify your DBA or out-of-hours team if there is a problem with your SQL Server replication or high-availability groups; for example if an automatic failover occurs, or an unacceptable number of queued transactions builds up at the distributor. Standard alerts by severity are too coarse; you are looking to build a customized set of alerts to convey accurate, specific information for each event. Check out this tip to learn more.
Solution
For this solution I am going to first explore the standard alert functionality offered by SQL Server Agent and show how to configure alerts for normal severity ranges. I’ll then look at sys.messages to examine the ranges of alerts I need for AlwaysOn availability groups, and show how to filter on a particular severity by keyword. I’ll also show a method of writing a script that will churn out the code to create new alerts automatically with little manual effort.
Disclaimer: All views here are my own and some readers may implement their alerting mechanisms differently. I do not claim this is the best approach – merely mine – and it is based on my interpretations of best practice and my experience as a DBA. YMMV!
SQL Server Alerts – Introduction
The alerts feature is provided in SQL Server Agent and serves as a mechanism to allow certain logged events to be captured by the Agent and actions taken after they are captured. These actions can be the execution of a job (and consequently a stored procedure); notification by email of one or more operators, or notification by net send or pager of one or more operators. This is useful if you need up-to-the-minute information on the health of your database systems, for example if you or your team are required to provide 24-hour support or you operate an OLTP system with minimal tolerated downtime.
Alerts are normally configured by the DBA alongside other features such as operators and Database Mail. These three keystones of the alerting feature are detailed extensively by other excellent writers so I will not detail them here. A walkthrough is provided by David Bird here (https://www.mssqltips.com/sqlservertip/1523/how-to-setup-sql-server-alerts-and-email-operator-notifications/) and is a good starting point for those who wish to know more. Alternatively consult Books Online for detailed information about the SQL Server Agent.
Configuring a Basic SQL Server Agent Alert
Below you’ll see how to configure a simple SQL Server Agent alert for all events categorized as Severity 16, notifying the DBA by email if an event is detected.
Using Object Explorer in SQL Server Management Studio, expand SQL Server Agent and right-click on Alerts. Click New Alert… (if you can’t expand SQL Server Agent, ensure the SQL Server Agent service is started for the instance you are using):
Fill in the fields in the first three tabs as follows:
Hit OK. Note I’ve already configured Database Mail and an operator called ‘Derek Colley’. Now test your alert using the following syntax:
RAISERROR('This is a test Severity 16 alert, please ignore.',16,1) WITH LOG;
If you don’t receive the error (and you’ve just configured Database Mail), right-click on SQL Server Agent in Object Explorer and select Properties. On the Alert System tab, tick the ‘Enable Mail Profile’ box, press OK then restart SQL Server Agent (for good measure):
And here is my error in my inbox:
Configuring Alerts for SQL Server AlwaysOn availability Groups – Filter by Keyword
As you’ve noticed in the section above, one way of configuring alerts is to use severity levels. However as the database product has expanded through versions 2000 to 2012, more and more potential error messages have been added to sys.messages (formerly sys.sysmessages) making the creation of specific alerts for specific error numbers a daunting task. For some classes of errors (independently of severity) you will note that various consecutive ranges of error number are used. This can be seen below.
Let’s have a look at sys.messages for alerts relating to the word ‘availability’ (as in, ‘AlwaysOn / AlwaysOn availability’):
SELECT message_id [error_number], severity, text
FROM sys.messages
WHERE text LIKE ('%availability%')
AND is_event_logged = 1;
This returns 48 rows in SQL Server 2012. You can further filter by language_id if required – English is 1033 – by adding it to the WHERE clause. This will return 17 rows for language_id = 1033.
You will immediately note that most of the messages are marked as Severity 10. Severity 10 is an *informational* message only and, while logged, will not normally trigger a database alert as most DBAs do not want an email on every Severity 10 – they are normally benign. Annoyingly, however, Microsoft has categorized some otherwise quite interesting errors that may indicate cluster or HA problems as Severity 10. So how do you capture these errors?
Go back to Object Explorer and right click Alerts, then hit New Alert… You will get the screen below. This time, tick ‘Raise alert when message contains:’ then type ‘availability’ (without quotes) in the text field. Ensure Severity 10 is highlighted in the drop-down menu above. Remember to fill in the Response and Options tabs to notify the DBA as shown in the basic example above, then click OK. You can test the alert using the following syntax:
RAISERROR('This is a test availability groups alert',10,1) WITH LOG;
You should see the alert appear in your inbox. You can customize the filter text to suit you – you may want to filter on ‘replication’, ‘cluster’, ‘corruption’, etc.
Configuring Alerts for AlwaysOn availability Groups – Specific Error Numbers
There’s another way of doing this too, depending on how finicky you are about having individual alerts for individual events. This isn’t strictly necessary, since choosing to include the error text in the alert in the New Alert dialog should ensure the error text makes it to your inbox. However, you may wish to have specific alerts so, for example, you can customize the subject field in the outbound email, or handle specific alerts differently depending on the exact problem.
First, let’s pick some alerts from sys.messages that look promising:
Here’s my code, which will create a script that you can run to create a new alert for each error message of interest to you (caution: dynamic SQL!)
SET NOCOUNT ON
-- first create a temporary table to store your target error numbers
DECLARE @errorNumbers TABLE ( ErrorNumber VARCHAR(6) )
INSERT INTO @errorNumbers
VALUES ('35273'),('35274'),('35275'),('35254'),('35279'),('35262'),('35276')
-- get the correct DB context
PRINT 'USE [msdb]'
PRINT 'GO'
PRINT '/* *************************************************************** */ '
-- use a cursor to iterate over each error number (yes, I know)...
DECLARE @thisErrorNumber VARCHAR(6)
DECLARE cur_ForEachErrorNumber CURSOR LOCAL FAST_FORWARD
FOR SELECT ErrorNumber FROM @errorNumbers
OPEN cur_ForEachErrorNumber
FETCH NEXT FROM cur_ForEachErrorNumber INTO @thisErrorNumber
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT
'EXEC msdb.dbo.sp_add_alert @name=N''HA Error - ' + @thisErrorNumber + ''',
@message_id=' + @thisErrorNumber + ',
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@job_id=N''00000000-0000-0000-0000-000000000000''
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N''HA Error - ' + @thisErrorNumber + ''',
@operator_name=N''Derek Colley'', @notification_method = 1
GO '
PRINT '/* *************************************************************** */ '
FETCH NEXT FROM cur_ForEachErrorNumber INTO @thisErrorNumber
END
CLOSE cur_ForEachErrorNumber
DEALLOCATE cur_ForEachErrorNumber
Now change the query output to text and execute the query. You’ll get the following output:
USE [msdb]
GO
/* *************************************************************** */
EXEC msdb.dbo.sp_add_alert @name=N'HA Error - 35273',
@message_id=35273,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'HA Error - 35273',
@operator_name=N'Derek Colley', @notification_method = 1
GO
/* *************************************************************** */
EXEC msdb.dbo.sp_add_alert @name=N'HA Error - 35274',
@message_id=35274,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'HA Error - 35274',
@operator_name=N'Derek Colley', @notification_method = 1
GO
/* *************************************************************** */
EXEC msdb.dbo.sp_add_alert @name=N'HA Error - 35275',
@message_id=35275,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'HA Error - 35275',
@operator_name=N'Derek Colley', @notification_method = 1
GO
/* *************************************************************** */
EXEC msdb.dbo.sp_add_alert @name=N'HA Error - 35254',
@message_id=35254,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'HA Error - 35254',
@operator_name=N'Derek Colley', @notification_method = 1
GO
/* *************************************************************** */
EXEC msdb.dbo.sp_add_alert @name=N'HA Error - 35279',
@message_id=35279,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'HA Error - 35279',
@operator_name=N'Derek Colley', @notification_method = 1
GO
/* *************************************************************** */
EXEC msdb.dbo.sp_add_alert @name=N'HA Error - 35262',
@message_id=35262,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'HA Error - 35262',
@operator_name=N'Derek Colley', @notification_method = 1
GO
/* *************************************************************** */
EXEC msdb.dbo.sp_add_alert @name=N'HA Error - 35276',
@message_id=35276,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'HA Error - 35276',
@operator_name=N'Derek Colley', @notification_method = 1
GO
/* *************************************************************** */
Now execute the script, and you’ll see your new HA alerts created under SQL Server Agent / Alerts in Object Explorer:
We can test this alert using the following syntax:
RAISERROR(35254,10,1) WITH LOG;
You should see the email hit your inbox. Note: Books Online warns that errors below Severity 19 (i.e. that are not written to the Windows Application Log) will not be alerted on. By specifying a particular alert by number, we have overridden this requirement – to verify, execute:
RAISERROR(35254,10,1);
And you will see another email dispatched to your inbox.
Next Steps
- Thanks for getting this far – if you’re interested in custom configuration of SQL Server Agent functionality, please see the links below for suggested reading:
- David Bird – MSSQLTips.com – How to Setup SQL Server Alerts and Email Operator Notifications
- Greg Robidoux – MSSQLTips.com – Setting up Database Mail for SQL 2005 (applies to later versions too)
- Jeremy Kadlec – MSSQLTips.com – SQL Server Database Backup and Restore Failure Notifications
- sys.messages on Books Online
- SQL Server Agent tips
