SQL Server Alert type detailed

An alert responds to a specific event. Alerts respond to the following event types:

1.SQL Server Event:

You can specify an alert to respond to one or more events. Use the following parameters to specify the event that triggers the alert:

Error number

The SQL Server agent issues an alert when a specific error occurs. For example, you can specify an error number 2571来 an operation that attempts to invoke the Database Console command (DBCC) without authorization.

Severity level

The SQL Server agent issues an alert when a critical error occurs at a specific level. For example, you can specify a severity level 15来 response to a syntax error in a Transact-SQL statement.

Database

SQL Server Agent alerts only when events occur in a particular database. This option is a supplement to the error number or severity level. For example, if the instance contains a database for production and a database for reporting, you can define alerts that respond only to syntax errors in the production database.

Event text

The SQL Server agent issues an alert when a specific text string is included in the event message for the specified event. For example, you can define alerts to respond to messages that contain specific table names or specific constraints.

2.SQL Server Performance Conditions

You can specify an alert to respond to a specific performance condition. In this case, you need to specify the performance counters to monitor, the thresholds for the alerts, and the actions that the counters must perform when the alert occurs. To set performance conditions, you must define the following items on the General page in the SQL Server Agent's new Alert or Alert Properties dialog box:

Object

object is the performance area to monitor.

Counter

The counter is the property of the area to be monitored.

Instance

The SQL Server instance defines a specific instance of the property to be monitored, if it exists.

"Triggers an alert when the counter meets the following conditions" and "value"

The threshold value of the alert and the behavior that caused the alert. The threshold value is a number. The behavior is one of the following: "Below", "equals", or "greater than" value specified. A value is a number that describes the performance condition counter. For example, to set an alert to occur for a performance object sqlserver:locks when Lock wait time exceeds 30 seconds, you can select greater than and specify 30 as value.

Also, for performance object sqlserver:transactions, you can specify an alert when free space in tempdb is below 1000 KB. To do this, you should select counters free spaces in tempdb (KB), "less than" and "value" 1000.

3.WMI Event

You can specify that an alert be issued to respond to a specific WMI event. To select a WMI event, you must define the following on the General page in the SQL Server Agent's new Alert or Alert Properties dialog box:

Name space

The SQL Server agent registers as a WMI client in the WMI namespace (using the namespace query event).

Inquire

SQL Server Agent uses the provided Windows Management Instrumentation Query Language (WQL) statement to identify specific events.

The following links point to common tasks:

Create an alert based on a message number

SQL Server Management Studio

Transact-SQL

SQL Server Management Objects (SMO)

Create alerts based on severity levels

SQL Server Management Studio

Transact-SQL

SQL Server Management Objects (SMO)

Create alerts based on WMI events

SQL Server Management Studio

Transact-SQL

SQL Server Management Objects (SMO)

Define a response to an alert

SQL Server Management Studio

Transact-SQL

SQL Server Management Objects (SMO)

Create an error message for a user-defined event

Transact-SQL

To modify an error message for a user-defined event

Transact-SQL

To delete an error message for a user-defined event

Transact-SQL

Disable or reactivate an alert

SQL Server Management Studio

Transact-SQL

SQL Server Management Objects (SMO)

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/