This article discusses details about SQL Server Alerts.
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:
- SQL Server event alert
- SQL Server Performance condition alert
- 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:
- Buffer Manager
- Buffer Partition
- Buffer Node
- General Statistics
- Access Methods
- SQL Errors
- SQL Statistics
- Plan Cache
- Cursor Manager by Type
- Cursor Manager Total
- Memory Manager
- User Settable
- Broker Statistics
- Broker / DBM Transport
- Broker Activation
- TO Statistics
- Wait Statistics
- Exec Statistics
- Catalog Metadata
- Deprecated Features
- Workload Group Stats
- 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:
- Execute any SQL Server Agent Job
- 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.
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.
It must be remembered that DBA has the option to enable / disable any or all of the alerts.
- 24th August, 2008: Initial post