Online Documentation for SQL Manager 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.

 

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

 

 

RadioButton Event alert

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

 

Alert Editor - Editing alert definition - Event Alert

 

 

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

 

RadioButton Error

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 and the drop-down list to select the Error message.

 

RadioButton 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 (see Using Navigation bar).

 

Error messages like

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

 

 

RadioButton Performance alert

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

 

Alert Editor - Editing alert definition - Performance Alert

 

 

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 when counter becomes / Value

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: < (falls below), = (becomes equal to), > (rises above). Use the Value edit-box to specify the threshold number that describes the performance condition counter.

 

 

RadioButton WMI alert

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

 

Alert Editor - Editing alert definition - WMI Alert

 

 

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. Such managed resources include hard drives, file systems, settings of operating system, processes, services, shares, registry settings, networking components, event logs, users, groups, etc.

WMI allows monitoring of performance counters as well. Microsoft® SQL Server™ has the WMI Event Provider built-in.

 

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'