Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Cost Effective SQL Server Transparent Data Encryption - Free Webinar
 

Configuring Critical SQL Server Alerts


By:   |   Last Updated: 2014-10-29   |   Comments (1)   |   Related Tips: More > 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:

The list of alerts

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

Checking the properties on a particular alert.

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.

Once the script is run you should see the Operator listed.

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

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


Last Updated: 2014-10-29


next webcast button


next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, November 13, 2014 - 10:50:14 AM - Kevin Back To Top

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.


Learn more about SQL Server tools