Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

SQL Server Alerts

4.50/5 (5 votes)
23 Aug 2008CPOL3 min read 1  
Defining SQL Server Alerts (shujaatsiddiqi.blogspot.com)

Introduction

This article discusses 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 a DBA wants to do some automatic management or get notified. SQL Server has a 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:

Image 1

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 object 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. The following stored procedure is 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.

History

  • 24th August, 2008: Initial post

License

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