Sample: Create a SQL Server Agent Alert with the WMI Provider
Applies to: SQL Server
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
AdventureWorks2022 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. SQL Server implicitly converts the string into XML format. Because the job step uses the Transact-SQL subsystem, the job step doesn't 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.
For SQL Server Agent to receive WMI events, Service Broker must be enabled in
USE AdventureWorks2022; GO IF OBJECT_ID('DeadlockEvents', 'U') IS NOT NULL DROP TABLE DeadlockEvents; 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 job step that inserts the current time and the deadlock graph into the
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'AdventureWorks2022'; 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
Test the sample
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 AdventureWorks2022; 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
USE AdventureWorks2022; 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 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
DeadlockGraph column should contain an XML document that shows all the properties of the deadlock graph event.