Alerting capabilities in SQL Server 2005

Alerts in SQL Server 2005 have surpassed the functionality of those in SQL Server 2000. It seems the latter version is meeting industry needs for an SMTP based email system with the introduction of Database Mail. In this tip Edgewood Solutions' Jeremy Kadlec outlines what you'll need to utilize the alerts.

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' 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

    Dig deeper on SQL Server Database Modeling and Design

    Pro+

    Features

    Enjoy the benefits of Pro+ membership, learn more and join.

    0 comments

    Oldest 

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to:

    -ADS BY GOOGLE

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close