Online Documentation for SQL Administrator for SQL Server

Setting alert properties


Use the Alert tab of Alert Editor to configure/view alert properties.

 

Name

Enter a name for the new alert, or modify the name of the alert being edited. Enable/disable the alert using the !CheckBox Enabled option.

 

Alert type

An alert responds to an event of a specific type: SQL server event, SQL server performance condition or WMI event.

 

!RadioButton SQL Server event alert

Specify properties for the alert that responds to a server event.

 

Alerts - Alert Editor

 

 

Properties:

Use the following parameters to specify the events that will trigger the alert:

 

!RadioButton Error number

Select this option to specify that SQL Server Agent will fire the alert when a specific error occurs. Use the spinner control to specify the Error ID.

 

!RadioButton Error severity

Select this option to specify that SQL Server Agent will fire the alert when any error of the specific severity occurs. Use the drop-down list to select the Severity level: 01-25. For example, you can specify a severity level of 15 to respond to syntax errors in Transact-SQL statements.

 

Database name

SQL Server Agent fires an alert only when the event occurs in a particular database. Select a database from the list of databases that reside on the specified SQL Server instance.

 

Alert only when message text contain

SQL Server Agent fires an alert when the specified event contains a particular text string in the event message.

 

 

!RadioButton SQL Server performance condition alert

Specify properties for the alert that responds to a SQL server performance condition.

 

Alert Editor - Setting server performance properties

 

 

Use the following parameters to specify the server performance conditions that will trigger the alert:

 

Object

Use the drop-down list to select the area of performance to be monitored.

 

Counter

Use the drop-down list to select the attribute of the area to be monitored.

 

Instance

Use the drop-down list to define the specific instance (if any) of the attribute to be monitored.

 

Alert if value is

Using these boxes you should specify the threshold for the alert and the behavior that produces the alert. Use the drop-down list to select the behavior value: less than (falls below), equal to (becomes equal to), more than (rises above). Use the Value edit-box to specify the threshold number that describes the performance condition counter.

 

 

!RadioButton WMI event alert

Specify properties for the alert that responds to a WMI event.

 

Alert Editor - Setting WMI alert properties

 

 

WMI is an acronym for Windows Management Instrumentation. WMI is the Microsoft's implementation of Web-Based Enterprise Management (WBEM) - a new management technology that allows software to monitor and control managed resources throughout the network.

 

One common way to use the WMI Event Provider is to create SQL Server Agent alerts that respond to specific events. SQL Server Agent submits a WQL request, receives WMI events, and runs a job in response to the event.

 

WMI namespace

Specify the WMI namespace on the machine where SQL Server is running.

 

WMI Query

Use this area to specify the WMI query text for the alert.

 

The WMI Query Language (WQL) is a subset of standard American National Standards Institute Structured Query Language (ANSI SQL) with minor semantic changes to support WMI.

 

Examples:

 

SELECT * FROM Win32_OperatingSystem WHERE Caption="Microsoft Windows XP Professional" AND CSDVersion="Service Pack 2"

 

If one needs to monitor the number of processes which use more than 10 threads, the following query can used:

SELECT ThreadCount FROM Win32_Process WHERE ThreadCount>10

 

The following WQL query retrieves specific event properties for any event that occurs in the AdventureWorks database and exists under the DDL_DATABASE_LEVEL_EVENTS event group:

SELECT SPID, SQLInstance, DatabaseName FROM DDL_DATABASE_LEVEL_EVENTS WHERE DatabaseName = 'AdventureWorks'