SQL Server Alerts

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:

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

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.

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.

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.

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

• 24^th August, 2008: Initial post