Defining Alerts
Events are generated by Microsoft SQL Server and entered into the Microsoft Windows application log. SQL Server Agent reads the application log and compares events written there to alerts that you have defined. When SQL Server Agent finds a match, it fires an alert, which is an automated response to an event. In addition to monitoring SQL Server events, SQL Server Agent can also monitor performance conditions and Windows Management Instrumentation (WMI) events.
To define an alert, you specify:
- The name of the alert.
- The event or performance condition that triggers the alert.
- The action that SQL Server Agent takes in response to the event or performance condition.
Naming an Alert
Every alert must have a name. Alert names must be unique within the instance of SQL Server and can be no longer than 128 characters.
Selecting an Event Type
An alert responds to an event of a specific type. Alerts respond to the following event types:
- SQL Server events
- SQL Server performance conditions
- WMI events
The type of the event determines the parameters that you use to specify the precise event.
Specifying a SQL Server Event
You can specify an alert to occur in response to one or more events. Use the following parameters to specify the events that trigger an alert:
- Error number
SQL Server Agent fires an alert when a specific error occurs. For example, you might specify error number 2571 to respond to unauthorized attempts to invoke Database Console Commands (DBCC). - Severity level
SQL Server Agent fires an alert when any error of the specific severity occurs. For example, you might specify a severity level of 15 to respond to syntax errors in Transact-SQL statements. - Database
SQL Server Agent fires an alert only when the event occurs in a particular database. This option applies in addition to the error number or severity level. For example, if an instance contains one database that is used for production and one database that is used for reporting, you can define an alert that responds to syntax errors in the production database only. - Event text
SQL Server Agent fires an alert when the specified event contains a particular text string in the event message. For example, you might define an alert that responds to messages that contain the name of a particular table or a particular constraint.
Selecting a Performance Condition
You can specify an alert to occur in response to a particular performance condition. In this case, you specify the performance counter to monitor, a threshold for the alert, and the behavior that the counter must show if the alert is to occur. To set a performance condition, you must define the following items on the SQL Server Agent General page of the New Alert or the Alert Properties dialog box:
Object
The object is the area of performance to be monitored.Counter
A counter is an attribute of the area to be monitored.Instance
The SQL Server instance defines the specific instance (if any) of the attribute to be monitored.Alert if counter and Value
The threshold for the alert and the behavior that produces the alert. The threshold is a number. The behavior is one of the following: falls below, becomes equal to, or rises above a number specified for Value. The Value is a number that describes the performance condition counter. For example, to set an alert to occur for the performance object SQLServer:Locks when the Lock Wait Time exceeds 30 minutes, you would choose rises above and specify 30 as the value.
As another example, you might specify that an alert occurs for the performance object SQLServer:Transactions when the free space in tempdb falls below 1000 KB. To set this, you would choose the counter Free space in tempdb (KB), falls below,and a Value of 1000.Note
Performance data is sampled periodically, which can lead to a small delay (a few seconds) between the threshold being reached and the occurrence of the performance alert.
Selecting a WMI Event
You can specify that an alert occur in response to a particular WMI event. To select a WMI event, you must define the following on the SQL Server Agent General page of the New Alert or the Alert Properties dialog box:
- Namespace
SQL Server Agent registers as a WMI client to the WMI namespace that is provided to query for events. - Query
SQL Server Agent uses the Windows Management Instrumentation Query Language (WQL) statement provided to identify the specific event.
Following are links to common tasks: