SQL Server Agent Alerts

Being a database administrator has many responsibilities, and knowing what is happening on your SQL Server is one of them. Being proactive and alerted to errors is one of the traits that makes someone a great DBA. And I’m not just talking about things failing, which is what most people think of being alerted about; you can also be alerted about performance problems. Within SQL Server you have the ability to create SQL Server Agent Alerts (which I’ll just call ‘alerts’ from now on), and this is easily accomplished using the GUI or T-SQL.

Configuring SQL Server Agent Alerts

To use alerts you must have Database Mail and a SQL Agent Operator configured. Most SQL instances I have come across already have Database Mail configured for job failure notifications. If you need further information on setting up this feature, visit the Books Online topic, "Configure Database Mail."

A lesser-known task is configuring the Operator. You can create the Operator using SSMS or T-SQL. Within SSMS expand SQL Server Agent, right click on Operator and chose New Operator. You will have a new dialog box open where you can give the operator a name and specify the email address to notify. I prefer to use a distribution group for the email notifications. Most companies have more than one person responsible for the SQL environment and if you specify a distribution group then the entire team can be notified of the alerts. Using distribution groups also makes it much easier to add or remove people from the alerts.

Below is an example screenshot of the New Operator dialog:

I prefer using T-SQL so I can make sure that creating the Operator is part of a server build template. Example code for creating the above Operator is as follows:

EXEC msdb.dbo.sp_add_operator @name = N'SQL_Alerts', 
  @enabled = 1, 
  @email_address = N'sql_alerts@mydomain.com';

Once you have Database Mail and the Operator configured you can create the alerts and assign them to the Operator.

If using SSMS, you can expand SQL Server Agent and then Alerts. By default, no alerts are created. If you right click and chose New Alert, you will get a screen similar to the figure below:

You will notice that under Severity, there are 25 severity codes. Just like it sounds, error level severity describes how important the error is. Severity 10 is informational while 19-25 are fatal and you will want to be notified when those errors arise. If a severity 23 error arose, for example, then you most likely have corruption in one of your databases. These fatal errors can all impact the performance of your server, which in turn impacts the customer experience.

There is an additional alert that you need to create, for error 825. Error 825, as Paul Randal describes in his blog post, is related to an I/O operation that SQL Server had to retry but that eventually succeeds (whereas errors 823 and 824 indicate that an I/O retry operation was retried and eventually failed). Error 825 is critical to know about because it is alerting you to I/O issues that could end up becoming fatal in future. Any retry attempt is bad, you shouldn’t wait until an I/O operation fails to be notified. If you start getting Error 825 messages, you need to immediately reach out to your storage and hardware teams.

You can create each of the Alerts by specifying the name and selecting the severity. For Error 825 you would select Error and type the number. As with the Operator, I prefer to use T-SQL. If I can easily script a process then it is much easier to reuse and include as part of a server build.
Below you will find the script that I have used on my SQL Server 2014 Developer workstation. This script creates each of the alerts and adds a notification for the alert to the Operator SQL_Alerts.

EXEC msdb.dbo.sp_add_alert @name = N'Severity 19 Error', 
  @message_id = 0,   @severity = 19,  @include_event_description_in = 0;

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 19 Error', 
  @operator_name = N'SQL_Alerts', @notification_method = 1;

EXEC msdb.dbo.sp_add_alert @name = N'Severity 20 Error', 
  @message_id = 0,   @severity = 20,  @include_event_description_in = 0;

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 20 Error', 
  @operator_name = N'SQL_Alerts', @notification_method = 1;

EXEC msdb.dbo.sp_add_alert @name=N'Severity 21 Error', 
  @message_id = 0,   @severity = 21,  @include_event_description_in = 0;

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 21 Error', 
  @operator_name = N'SQL_Alerts', @notification_method = 1;

EXEC msdb.dbo.sp_add_alert @name = N'Severity 22 Error', 
  @message_id = 0,   @severity = 22,  @include_event_description_in = 0;

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 22 Error', 
  @operator_name = N'SQL_Alerts', @notification_method = 1;

EXEC msdb.dbo.sp_add_alert @name = N'Severity 23 Error', 
  @message_id = 0,   @severity = 23,  @include_event_description_in = 0;

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 23 Error', 
  @operator_name = N'SQL_Alerts', @notification_method = 1;

EXEC msdb.dbo.sp_add_alert @name = N'Severity 24 Error', 
  @message_id = 0,   @severity = 24,  @include_event_description_in = 0;

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 24 Error', 
  @operator_name = N'SQL_Alerts', @notification_method = 1;

EXEC msdb.dbo.sp_add_alert @name = N'Severity 25 Error', 
  @message_id = 0,   @severity = 25,  @include_event_description_in = 0;

EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 25 Error',
  @operator_name = N'SQL_Alerts', @notification_method = 1;

EXEC msdb.dbo.sp_add_alert @name = N'Error 825', 
  @message_id = 825,  @severity = 0,  @include_event_description_in = 0;

EXEC msdb.dbo.sp_add_notification @alert_name = N'Error 825',
  @operator_name = N'SQL_Alerts', @notification_method = 1;

If you have followed along, you would have database mail configured, created an Operator to email you or a distribution group about potential errors, and SQL Server Agent Alerts configured for Severity 19 – 25 and error 825.

This is great. Any time one of those alerts are triggered an email will be sent to your team. In addition to event alerts, alerts can be configured for a performance condition, as I mentioned in the introduction. For example, if memory usage exceeds a defined threshold an alert could be triggered. I encourage you to explore the various performance alerts and create the ones your organization could benefit from. To find the SQL Server performance condition alerts, in the new alert dialog box, click the drop down box for Type. There you will see SQL Server performance condition alert listed. Once you chose that option you can browse the types of objects you can configure a performance condition alert on.

While we assigned an Operator to the alert response, you could also configure the alert to execute a SQL Agent job. While this gives you some flexibility to have event response task, it doesn’t provide the ability to have easy conditional alerting.

Using SQL Sentry for Advanced Alerting

For more advanced alerting, you need a better tool. This is where SQL Sentry can help. One of my favorite SQL Sentry alerting features is the ability to create custom conditions to alert or act when something has changed within the environment. For example, if someone changed the min or max memory value, modified maxdop or the cost threshold for parallelism you could get an alert or even kick off a process. This feature was introduced in SQL Sentry v8, and Greg Gonzalez (blog | @SQLsensei) blogged about it here: "SQL Sentry v8: Intelligent Alerting Redefined."

With this feature, you can also create custom conditions for different databases within a single alert. If you attempted this using SQL Agent alerts you would have to create different alerts per database.

Another great alerting feature is the ability to create different alerting schedules. Many organizations have teams that are responsible during different parts of the day. Some may have the production DBA’s responsible during the daytime hours with a Network Operations Center covering the night shift, then an on-call person over the weekends. Wouldn’t it be great to be able to customize an alerting schedule to notify the proper teams during their hours of responsibility?

You can create Alert Windows (as in a window of time) and tie those to different alerts or groups. This allows different alerts to be active during different times and for different groups to be notified at different times. This is really cool as it lets your alerting follow a support schedule so the correct people are notified. Scott Fallen details this feature in a blog post, "Alerting on a Call Schedule with SQL Sentry," walking you through creating alerts for various on-call teams.

Another alerting feature of Performance Advisor and Event Manager is the ability to configure other responses such as executing a Windows process, logging the event to a database or error log, sending an SNMP trap to another monitoring tool such as SCOM, or even killing a process. Your options are almost limitless as to what you can have predefined to happen when a certain event occurs. SQL Agent Alerts are not that customizable.

Summary

The important take away from this post is that you absolutely need to be alerting for errors and performance conditions. If you don’t have a tool such as SQL Sentry then utilizing SQL Agent Alerts is still a great start.

Over my next few posts, I’ll be diving into some of these performance impacting alerts and discussing what actions you would need to take when they arise.