Sample: Creating a SQL Server Agent Alert by Using the WMI Provider for Server Events

One common way to use the WMI Event Provider is to create SQL Server Agent alerts that respond to specific events. The following sample presents a simple alert that saves XML deadlock graph events in a table for later analysis. SQL Server Agent submits a WQL request, receives WMI events, and runs a job in response to the event. Notice that, although several Service Broker objects are involved in processing the notification message, the WMI Event Provider handles the details of creating and managing these objects.

First, a table is created in the AdventureWorks2008R2 database to hold the deadlock graph event. The table contains two columns: The AlertTime column holds the time that the alert runs, and the DeadlockGraph column holds the XML document that contains the deadlock graph.

Then, the alert is created. The script first creates the job that the alert will run, adds a job step to the job, and targets the job to the current instance of SQL Server. The script then creates the alert.

The job step retrieves the TextData property of the WMI event instance and inserts that value into the DeadlockGraph column of the DeadlockEvents table. Notice that SQL Server implicitly converts the string into XML format. Because the job step uses the Transact-SQL subsystem, the job step does not specify a proxy.

The alert runs the job whenever a deadlock graph trace event would be logged. For a WMI alert, SQL Server Agent creates a notification query using the namespace and WQL statement specified. For this alert, SQL Server Agent monitors the default instance on the local computer. The WQL statement requests any DEADLOCK_GRAPH event in the default instance. To change the instance that the alert monitors, substitute the instance name for MSSQLSERVER in the @wmi_namespace for the alert.

Note

For SQL Server Agent to receive WMI events, Service Broker must be enabled in msdb and AdventureWorks2008R2.

To see the job run, provoke a deadlock. In SQL Server Management Studio, open two SQL Query tabs and connect both queries to the same instance. Run the following script in one of the query tabs. This script produces one result set and finishes.

Run the following script in the first query tab. This script blocks, waiting to acquire a lock on Production.Location. After a short time-out, SQL Server will choose either this script or the script in the sample as the deadlock victim and end the transaction.