5,665,355 members and growing! (16,056 online)
Email Password   helpLost your password?
Database » Database » SQL Server     Beginner License: The Code Project Open License (CPOL)

SQL Server Alerts

By Muhammad Shujaat Siddiqi

Defining SQL Server Alerts (shujaatsiddiqi.blogspot.com)
SQL, SQL Server (SQL 2000, SQL 2005, SQL Server), SysAdmin, DBA

Posted: 23 Aug 2008
Updated: 23 Aug 2008
Views: 2,575
Bookmarked: 6 times
Announcements
Loading...



Search    
Advanced Search
Sitemap
4 votes for this Article.
Popularity: 2.41 Rating: 4.00 out of 5
0 votes, 0.0%
1
0 votes, 0.0%
2
1 vote, 25.0%
3
1 vote, 25.0%
4
2 votes, 50.0%
5
Note: This is an unedited contribution. If this article is inappropriate, needs attention or copies someone else's work without reference then please Report This Article

Introduction

This article discusses the details about SQL Server Alerts.

Background

It is not possible for DBAs to check continuously for every special condition. There may be special conditions in which DBA wants to do some automatic management or get notified. SQL Server has feature called ALERTS to cope with this requirement.

Types of Alerts:

There are three types of alerts:

  1. SQL Server event alert
  2. SQL Server Performance condition alert
  3. WMI event alert

SQL Server Event Alerts:

These errors may be defined for specific Error Numbers or Severity levels. There are 25 different severity levels for alert. The details are as follows:

These alerts may be defined specifically for any particular database or generically for all the databases.

2.jpg

SQL Server Performance Condition Alerts

These are SQL Server alerts which are generated because of some performance condition of the instance. There are various objects for which these alerts may be generated. They are as follows:

  1. Buffer Manager
  2. Buffer Partition
  3. Buffer Node
  4. General Statistics
  5. Locks
  6. Databases
  7. Latches
  8. Access Methods
  9. SQL Errors
  10. SQL Statistics
  11. Plan Cache
  12. Cursor Manager by Type
  13. Cursor Manager Total
  14. Memory Manager
  15. User Settable
  16. Transactions
  17. Broker Statistics
  18. Broker / DBM Transport
  19. Broker Activation
  20. TO Statistics
  21. Wait Statistics
  22. Exec Statistics
  23. CLR
  24. Catalog Metadata
  25. Deprecated Features
  26. Workload Group Stats
  27. Resource Pool Stats

There are various different counters associated with every objects specified above. E.g. a database alert may be generated when the usage of log file becomes equal to any specific values. Basically, it may be checked for each counter to become equal to, greater than or less than any specific value.

perf.jpg

WMI (Windows Management Instrumentation) Alert:

As you know WMI Scripts are written to automate administrative tasks on Windows based environment about management of data. WMI query may also be specified to define alert. For these types of alert, namespace and WMI script query is specified.

4.jpg

Response of Alert

As a result of the alert two types of responses may be generated. Remember that both of these responses are not mutually exclusive i.e. any or both of the two responses may be generated as a result of the alert:

  1. Execute any SQL Server Agent Job
  2. Notify operator:
The operator may be notified through email, pager or net send message. The DBA has the option to select any already existing operator or define a new operator. Some delay may also be defined between each alert. Some specific messages may also be added with the response. These responses may also be defined with the error message for the alert. The alerts may also be subjected to specific text of error message for the alert

5.jpg

T-SQL Support

Now you must be wondering whether any T-SQL support is available to define these alerts or not. So the answer is YES. There are following stored procedures available:

msdb.dbo.sp_add_alert

This stored procedure is used to define any new alert.

Note:

It must be remembered that DBA has the option to enable / disable any or all of the alerts.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Muhammad Shujaat Siddiqi


Muhammad Shujaat Siddiqi

BE-Computer & Information Systems Engineering
NED University of Engineering & Technology Karachi, Pakistan.

MBA
Institute of Business Administration, Karachi.
Occupation: Software Developer (Senior)
Location: United States United States

Other popular Database articles:

Article Top
Sign Up to vote for this article
You must Sign In to use this message board.
FAQ FAQ Noise ToleranceSearch Search Messages 
 Layout  Per page   
 Msgs 1 to 1 of 1 (Total in Forum: 1) (Refresh)FirstPrevNext
GeneralInterestingmemberPaul Conrad9:03 24 Aug '08  

General General    News News    Question Question    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

PermaLink | Privacy | Terms of Use
Last Updated: 23 Aug 2008
Editor:
Copyright 2008 by Muhammad Shujaat Siddiqi
Everything else Copyright © CodeProject, 1999-2008
Web15 | Advertise on the Code Project