Configuring Critical SQL Server Alerts

By: K. Brian Kelley | Updated: 2014-10-29 | Comments (2) | Related: > SQL Server Agent

Problem

I ran a couple of health checks on my SQL Servers and the checks indicated I didn't have critical alerts configured. What do I need to do?

Solution

SQL Server has alerts that get more important based on the severity of the alert. Anything of severity 16 or below tends to refer to the database and deals with issues that are tied to syntax errors, violations of foreign keys, etc. While those errors are typically important, they don't refer to anything with regards to overall health of the SQL Server. Alerts 17 through 25 do. Those are the ones your health checks are probably firing on. So what are these alerts?

Severity Level	Meaning
17	Insufficient Resources
18	Nonfatal Internal Error Detected
19	SQL Server Error in Resource
20	SQL Server Fatal Error in Current Process
21	SQL Server Fatal Error in Database (dbid) Process
22	SQL Server Fatal Error Table Integrity Suspect
23	SQL Server Fatal Error: Database Integrity Suspect
24	Hardware Error
25	(no description)

As you might guess, these are errors you want to alert on because they either represent a resource issue, an integrity issue, or a hardware issue. Here's what you'll need to do to setup alerts on your SQL Servers. While you can do this via the GUI, if you have everything scripted, you can quickly run the scripts for any SQL Server you need to set up.

Configuring SQL Server Database Mail

In order to be able to receive emails when an alert fires, you need to set up database mail. You're going to need a few bits of information:

The email address to use. This can be a "dummy" address, but you should have a good reply to email address.
The mail server to connect to.
Any authentication requirements for the mail server.

Here's a generic configuration. Note that I'm using a dummy address to send from, but the reply address should go back to the DBAs. I've specified these values as variables in the TODO section so that I can re-use the script and only make a couple of changes depending on the environment.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

DECLARE @pid INT;
DECLARE @acctid INT;

EXEC msdb.dbo.sysmail_add_profile_sp
  @profile_name = 'Default Profile',
  @profile_id = @pid OUTPUT;

EXEC msdb.dbo.sysmail_add_account_sp 
  @account_name = 'Default Account',
  @email_address = '[email protected]',
  @display_name = 'SQL - myserver',
  @replyto_address = '[email protected]',
  @mailserver_name = 'smtp.mycompany.com',
  @account_id = @acctid OUTPUT;

EXEC msdb.dbo.sysmail_add_profileaccount_sp
  @profile_id = @pid,
  @account_id = @acctid, 
  @sequence_number = 1;
GO

Setting Up SQL Server Agent Alerts

With Database Mail configured, it's time to set up the alerts. Here's a simple, default script. If you're worried about a lot of alerts firing all at once, you can set a delay by changing the @delay_between_responses parameter. Note that I am setting the @include_event_description_in parameter to 1, meaning however the alert sends to an operator, the details of the alert will be included. Since we'll be using email, that means the details of why the alert fired will be included in the email message body.

USE [msdb]
GO

EXEC msdb.dbo.sp_add_alert @name=N'Error 17 Alert', 
  @message_id=0, 
  @severity=17, 
  @enabled=1, 
  @delay_between_responses=0, 
  @include_event_description_in=1;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Error 18 Alert', 
  @message_id=0, 
  @severity=18, 
  @enabled=1, 
  @delay_between_responses=0, 
  @include_event_description_in=1;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Error 19 Alert', 
  @message_id=0, 
  @severity=19, 
  @enabled=1, 
  @delay_between_responses=0, 
  @include_event_description_in=1;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Error 20 Alert', 
  @message_id=0, 
  @severity=20, 
  @enabled=1, 
  @delay_between_responses=0, 
  @include_event_description_in=1;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Error 21 Alert', 
  @message_id=0, 
  @severity=21, 
  @enabled=1, 
  @delay_between_responses=0, 
  @include_event_description_in=1;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Error 22 Alert', 
  @message_id=0, 
  @severity=22, 
  @enabled=1, 
  @delay_between_responses=0, 
  @include_event_description_in=1;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Error 23 Alert', 
  @message_id=0, 
  @severity=23, 
  @enabled=1, 
  @delay_between_responses=0, 
  @include_event_description_in=1;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Error 24 Alert', 
  @message_id=0, 
  @severity=24, 
  @enabled=1, 
  @delay_between_responses=0, 
  @include_event_description_in=1;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Error 25 Alert', 
  @message_id=0, 
  @severity=25, 
  @enabled=1, 
  @delay_between_responses=0, 
  @include_event_description_in=1;
GO

You should see the list of alerts:

Checking the properties on a particular alert, you should see the severity matches the name:

Configuring the SQL Server Operator

The alert will need to go somewhere. That's what configuring an Operator is for. Here's a generic operator for DBAs. Do note that the name of the operator and the email are being set by variables. Again, this is to be able to re-use the script across multiple servers/environments. It does appear a bit lengthy, but that's because you have to associate a notification with each alert.

USE msdb;
GO
EXEC msdb.dbo.sp_add_operator
  @name = 'DBAs',
  @enabled = 1,
  @email_address = '[email protected]';
GO 

EXEC msdb.dbo.sp_add_notification 
  @alert_name = N'Error 17 Alert',
  @operator_name = 'DBAs',
  @notification_method = 1;
GO

EXEC msdb.dbo.sp_add_notification 
  @alert_name = N'Error 18 Alert',
  @operator_name = 'DBAs',
  @notification_method = 1;
GO

EXEC msdb.dbo.sp_add_notification 
  @alert_name = N'Error 19 Alert',
  @operator_name = 'DBAs',
  @notification_method = 1;
GO

EXEC msdb.dbo.sp_add_notification 
  @alert_name = N'Error 20 Alert',
  @operator_name = 'DBAs',
  @notification_method = 1;
GO

EXEC msdb.dbo.sp_add_notification 
  @alert_name = N'Error 21 Alert',
  @operator_name = 'DBAs',
  @notification_method = 1;
GO

EXEC msdb.dbo.sp_add_notification 
  @alert_name = N'Error 22 Alert',
  @operator_name = 'DBAs',
  @notification_method = 1;
GO

EXEC msdb.dbo.sp_add_notification 
  @alert_name = N'Error 23 Alert',
  @operator_name = 'DBAs',
  @notification_method = 1;
GO

EXEC msdb.dbo.sp_add_notification 
  @alert_name = N'Error 24 Alert',
  @operator_name = 'DBAs',
  @notification_method = 1;
GO

EXEC msdb.dbo.sp_add_notification 
  @alert_name = N'Error 25 Alert',
  @operator_name = 'DBAs',
  @notification_method = 1;
GO

Once the script is run you should see the Operator listed and you can check its properties to ensure the email address is set.

Also, you can verify that all the alert notifications are configured.

Verify SQL Server Agent Status

One last thing: make sure SQL Server Agent is stared and configured to start automatically. SQL Server Agent is what drives this alerting. If it's not running and you do have an issue, you won't get the email. Therefore, check your SQL Server Agent configuration closely.

Next Steps

Learn how to configure other alerts using the GUI.
See all the tips regarding Database Mail.
Read through all the monitoring tips for SQL Server.
Check out the SQL Server Agent Tips.

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2014-10-29

Tuesday, February 13, 2024 - 1:50:55 AM - abhishek	Back To Top (91942)
Is it possible to configure it without enabling database mails, considering that it is recommended to have them disabled as per best practices?

Thursday, November 13, 2014 - 10:50:14 AM - Kevin	Back To Top (35277)
Great article, Brian! I learned all of this months ago... with a couple of hours of trial and error... with the help of dozens of different web articles. In my searching I did not come across a single article as concise and helpful as this.