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

First, a table is created in the AdventureWorks 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 2005. 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 2005 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, SQL Server 2005 Service Broker must be enabled in msdb and AdventureWorks.

USE AdventureWorks ;
GO

IF OBJECT_ID('DeadlockEvents', 'U') IS NOT NULL
BEGIN
    DROP TABLE DeadlockEvents ;
END ;
GO

CREATE TABLE DeadlockEvents
    (AlertTime DATETIME, DeadlockGraph XML) ;
GO
-- Add a job for the alert to run.

EXEC  msdb.dbo.sp_add_job @job_name=N'Capture Deadlock Graph', 
    @enabled=1, 
    @description=N'Job for responding to DEADLOCK_GRAPH events' ;
GO

-- Add a jobstep that inserts the current time and the deadlock graph into
-- the DeadlockEvents table.

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'Capture Deadlock Graph',
    @step_name=N'Insert graph into LogEvents',
    @step_id=1, 
    @on_success_action=1, 
    @on_fail_action=2, 
    @subsystem=N'TSQL', 
    @command= N'INSERT INTO DeadlockEvents
                (AlertTime, DeadlockGraph)
                VALUES (getdate(), N''$(ESCAPE_SQUOTE(WMI(TextData))))',
    @database_name=N'AdventureWorks' ;
GO

-- Set the job server for the job to the current instance of SQL Server.

EXEC msdb.dbo.sp_add_jobserver @job_name = N'Capture Deadlock Graph' ;
GO

-- Add an alert that responds to all DEADLOCK_GRAPH events for
-- the default instance. To monitor deadlocks for a different instance,
-- change MSSQLSERVER to the name of the instance.

EXEC msdb.dbo.sp_add_alert @name=N'Respond to DEADLOCK_GRAPH', 
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', 
    @wmi_query=N'SELECT * FROM DEADLOCK_GRAPH', 
    @job_name='Capture Deadlock Graph' ;
GO

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.

USE AdventureWorks ;
GO

BEGIN TRANSACTION ;
GO

SELECT TOP(1) Name FROM Production.Product WITH (XLOCK) ;
GO

Run the following script in the second query tab. This script produces one result set and then blocks, waiting to acquire a lock on Production.Product.

USE AdventureWorks ;
GO

BEGIN TRANSACTION ;
GO

SELECT TOP(1) Name FROM Production.Location WITH (XLOCK) ;
GO

SELECT TOP(1) Name FROM Production.Product WITH (XLOCK) ;
GO

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 2005 will choose either this script or the script in the sample as the deadlock victim and end the transaction.

SELECT TOP(1) Name FROM Production.Location WITH (XLOCK) ;
GO

After provoking the deadlock, wait several moments for SQL Server Agent to activate the alert and run the job. Examine the contents of the DeadlockEvents table by running the following script:

SELECT * FROM DeadlockEvents ;
GO

The DeadlockGraph column should contain an XML document that shows all the properties of the deadlock graph event.

Concepts

Help and Information