Home > SQL Server Tips > > Alerting capabilities in SQL Server 2005
SQL Server Tips:
EMAIL THIS
 TIPS & NEWSLETTERS TOPICS 


Alerting capabilities in SQL Server 2005


Jeremy Kadlec, Edgewood Solutions
08.31.2006
Rating: -3.80- (out of 5)


Expert advice on database administration
Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us    Add to Google


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.


    Rate this Tip
    To rate tips, you must be a member of SearchSQLServer.com.
    Register now to start rating these tips. Log in if you are already a member.




    Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us    Add to Google


    RELATED CONTENT
    SQL Server database design and modeling
    Check SQL Server database and log file size with this stored procedure
    SQL Server tempdb best practices increase performance
    FAQ: SQL Server databases how-to
    How to maintain SQL Server indexes for query optimization
    How to retrieve SQL Server database disk space in use
    Maintain large SQL Server database and resolve website 'Timeout Error'
    How to construct and use SQL OUTER JOINs optimally
    How to use the LEFT vs. RIGHT OUTER JOIN in SQL
    Using the FULL OUTER JOIN in SQL
    SQL OUTER JOIN sample statements for queries

    SQL Server installation
    Virtual database storage for SQL Server: Friend or foe?
    Tutorial: Migrating to SANs from local SQL Server disk storage
    How to restore SQL Server database to transition server during upgrade
    Storage area network (SAN) basics every SQL Server DBA must know
    Tips for moving from SQL Server local disk storage to SANs
    SQL Server 2005 log shipping setup using the wizard
    SQL Server tools don't appear in menu after SQL Server 2005 install
    Troubleshoot SQL Server 2005 SP2 installation error
    Configuring SQL Server memory settings
    Optimize SAN setup for improved SQL Server performance
    SQL Server installation Research

    SQL Server performance and tuning
    Virtual database storage for SQL Server: Friend or foe?
    Tutorial: SQL Server 2005 Analysis Services
    Tutorial: Migrating to SANs from local SQL Server disk storage
    SQL Server memory configurations for procedure cache and buffer cache
    Using the OUTPUT clause for practical SQL Server applications
    Check SQL Server database and log file size with this stored procedure
    SQL Server PerfMon counters for access methods and buffer manager
    Find size of SQL Server tables and other objects with stored procedure
    Monitor SQL Server disk I/O with PerfMon counters
    SQL Server tempdb best practices increase performance

    RELATED GLOSSARY TERMS
    Terms from Whatis.com − the technology online dictionary
    binary tree  (SearchSQLServer.com)
    block  (SearchSQLServer.com)
    data structure  (SearchSQLServer.com)
    DDBMS  (SearchSQLServer.com)
    entity-relationship model  (SearchSQLServer.com)
    initial extent  (SearchSQLServer.com)
    primary key  (SearchSQLServer.com)
    segment  (SearchSQLServer.com)
    tablespace  (SearchSQLServer.com)
    view  (SearchSQLServer.com)

    RELATED RESOURCES
    2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems
    Search Bitpipe.com for the latest white papers and business webcasts
    Whatis.com, the online computer dictionary

    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.

    HomeNewsTopicsITKnowledge ExchangeTipsAsk the ExpertsMultimediaWhite PapersIT Downloads
    About Us  |  Contact Us  |  For Advertisers  |  For Business Partners  |  Site Index  |  RSS
    SEARCH 
    TechTarget provides enterprise IT professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective IT purchase decisions and managing their organizations' IT projects - with its network of technology-specific Web sites, events and magazines.

    TechTarget Corporate Web Site  |  Media Kits  |  Reprints  |  Site Map




    All Rights Reserved, Copyright 2005 - 2008, TechTarget | Read our Privacy Policy
      TechTarget - The IT Media ROI Experts