Alerting capabilities in SQL Server 2005

Jeremy Kadlec, Edgewood Solutions
Introduction

Native alerts were valuable in SQL Server 2000, but at the same time they were unreliable in many cases due to SQL Server Mail's dependency on MAPI with Outlook. Many 'work-arounds'

    Requires Free Membership to View

    Login

were created to improve the inherent functionality based on individual needs. SQL Server 2005 has alleviated this issue with the introduction of Database Mail. Thus far, this solution seems to meet industry needs for an SMTP based email system and has enabled the native SQL Server alerting capabilities to shine. In this tip we will outline the prerequisites for alerts, setup options and then highlight valuable alerts you'll want.

SQL Server 2005 alerts prerequisites

SQL Server 2005 Alerts are dependent on a few core pieces of infrastructure to operate properly. These items include:

ID Directions Screen Shot\Code
1 Enable the database mail stored procedures by executing the following code in a query window in SQL Server 2005 Management Studio. 
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
2 Setup Database Mail with accounts to associate with an Operator and to support receiving alerts.

Below are the directions to start the Database Mail Configuration Wizard:

  1. Open the SQL Server 2005 Management Studio
  2. In the Object Explorer, expand the 'Management' folder
  3. Right click on the 'Database Mail' option
  4. Select the 'Configure Database Mail' option
3 Setup Operators to receive alerts.

Below are the directions to setup SQL Server Operators:

  1. Open the SQL Server 2005 Management Studio
  2. In the Object Explorer, expand the 'SQL Server Agent' option
  3. Right click on the 'Operators' folder
  4. Select the 'New Operator' option
  5. Complete the interface on the right for the operator
4 Configure SQL Server Agent to use the mail session as well as have a fail-safe operator.

Below are the directions to configure SQL Server Agent:

  1. Open the SQL Server 2005 Management Studio
  2. In the Object Explorer, right click on the 'SQL Server Agent' option
  3. Select the 'Properties' option
  4. Select the 'Alert System' tab
  5. Complete the interface on the right

SQL Server 2005 alerts setup

SQL Server has two means to setup native alerts. The first is with SQL Server 2005 Management Studio and the second is via a set of system stored procedures. Below outlines examples of each option:

ID Option Screen shot/code
1a SQL Server 2005 Management Studio Interface to setup alerts

General Tab – Configure the name, severity, etc on this interface

Below are the directions to configure SQL Server 2005 alerts:

  1. Open the SQL Server 2000 Management Studio
  2. In the Object Explorer, open the 'SQL Server Agent' option
  3. Right click on the 'Alerts' folder
  4. Select the 'New Alert' option
  5. Complete the interface on the right
1b Response Tab – Select the operators to notify
1c Options Tab – Indicate the options when the text will be included and the delayed response parameter
2 Two SQL Server 2005 system stored procedures are responsible for creating alerts and notifying operators.

These procedures are:

  • sp_add_alert
  • sp_add_notification
    		•  
    USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 
20 Error', 
  @message_id=0, 
  @severity=20, 
  @enabled=1, 
  @delay_between_responses=180, 
  @include_event_description_in=7, 
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO

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

    Valuable SQL Server 2005 Alerts

    Below are three basic approaches to building a set of needed SQL Server 2005 alerts, since none are created by default:

    ID Approach Sample code
    1 Category

    For example, -- the Severity Level is considered a Fatal Error for levels 19 to 25. 

    -- Level 19
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 
19 Error', 
  @message_id=0, 
  @severity=19, 
  @enabled=1, 
  @delay_between_responses=180, 
  @include_event_description_in=7, 
  @category_name=N'[Uncategorized]', 
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO

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

-- Level 20
EXEC msdb.dbo.sp_add_alert @name=N'Severity 
20 Error', 
  @message_id=0, 
  @severity=20, 
  @enabled=1, 
  @delay_between_responses=180, 
  @include_event_description_in=7, 
  @category_name=N'[Uncategorized]', 
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO

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

-- Level 21
EXEC msdb.dbo.sp_add_alert @name=N'Severity 
21 Error', 
  @message_id=0, 
  @severity=21, 
  @enabled=1, 
  @delay_between_responses=180, 
  @include_event_description_in=7, 
  @category_name=N'[Uncategorized]', 
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO

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

-- Level 22
EXEC msdb.dbo.sp_add_alert @name=N'Severity 
22 Error', 
  @message_id=0, 
  @severity=22, 
  @enabled=1, 
  @delay_between_responses=180, 
  @include_event_description_in=7, 
  @category_name=N'[Uncategorized]', 
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO

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

-- Level 23
EXEC msdb.dbo.sp_add_alert @name=N'Severity 
23 Error', 
  @message_id=0, 
  @severity=23, 
  @enabled=1, 
  @delay_between_responses=180, 
  @include_event_description_in=7, 
  @category_name=N'[Uncategorized]', 
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO

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

-- Level 24
EXEC msdb.dbo.sp_add_alert @name=N'Severity 
24 Error', 
  @message_id=0, 
  @severity=24, 
  @enabled=1, 
  @delay_between_responses=180, 
  @include_event_description_in=7, 
  @category_name=N'[Uncategorized]', 
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO

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

-- Level 25
EXEC msdb.dbo.sp_add_alert @name=N'Severity 
25 Error', 
  @message_id=0, 
  @severity=25, 
  @enabled=1, 
  @delay_between_responses=180, 
  @include_event_description_in=7, 
  @category_name=N'[Uncategorized]', 
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_add_notification 
@alert_name=N'Severity 25 Error', 
@operator_name=N'DBATeam', 
@notification_method =1
GO
    2 Keyword

    For example, 'Primary Filegroup is full' when the database can no longer allocate space for the database. 

    USE [msdb]
GO

EXEC msdb.dbo.sp_add_alert @name=N'Keyword 
- Primary Filegroup is full', 
  @message_id=0, 
  @severity=1, 
  @enabled=1, 
  @delay_between_responses=180, 
  @include_event_description_in=7, 
  @event_description_keyword=N'Primary Filegroup is full', 
  @category_name=N'[Uncategorized]', 
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'Keyword 
- Primary Filegroup is full', 
@operator_name=N'DBATeam', 
@notification_method =1
GO
    3 Error Number

    For example, error 9100 related to index corruption. 

    USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error - 9100 
(Index Corruption)', 
  @message_id=9100, 
  @severity=0, 
  @enabled=1, 
  @delay_between_responses=180, 
  @include_event_description_in=7, 
  @category_name=N'[Uncategorized]', 
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_add_notification 
@alert_name=N'Error - 9100 
(Index Corruption)', 
@operator_name=N'DBATeam', 
@notification_method =1
GO

    Duplicating the alerts

    Once you have all of the alerts that you want on one specific SQL Server, they can be scripted out and created on another SQL Server. This can be accomplished by the following steps:

    • Open the SQL Server 2005 Management Studio
    • In the Object Explorer, open the 'SQL Server Agent' option
    • Expand the 'Alerts' folder
    • Select any alert that you want to script out
    • Right click on the alert and select the following options
      • 'Script Alert as'
      • 'CREATE To'
      • 'New Query Editor Window'
    • Connect to the needed SQL Server, then Copy the code from the original window and paste the code into the correct SQL Server's query window
    Conclusion

    SQL Server 2005's alerting capabilities are expected to meet the much desired features of the SQL Server community. Based on your application needs configure your alerts to be the first line of defense in if SQL Server has an issue. Do not overlook this simple yet valuable means to obtain near real time alerts.

    About the author: Jeremy Kadlec is the Principal Database Engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server Users Groups and nationally at SQL PASS. He is the author of the Rational Guide to IT Project Management. Jeremy is also the SearchSQLServer.com Performance Tuning expert. Ask him a question here.


    This was first published in August 2006

    Join the conversationComment

    Share
    Comments

      Results

      Contribute to the conversation

      All fields are required. Comments will appear at the bottom of the article.

      Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

      Back to top