WMI Event Alerts

In my previous article on Event Notifications I showed you how to create and event notification for the DEADLOCK_GRAPH event, and how the deadlock could be extracted from the a Service Broker queue that you have created. The example was incomplete in that it did not show how to remove the entries from the queue, and therefore each time you had a deadlock the queue would grow.

Using WMI Event Alerts for Server Events you don’t have to worry about creating a Service Broker queue as the process uses the Service Broker Service SQL/Notifications/ProcessWMIEventProviderNotification/v1.0 in msdb. A fresh installation of SQL Server 2005 or 2008 will have Service Broker enabled for msdb, but if you are not sure you can check with the query I posted in the Event Notification article.

To learn more about WMI Events for Server Events check out the topic “Understanding the WMI Provider for Server Events” in Books Online.

When writing a system to monitor and archive deadlock graphs, the first thing you will need is a repository. For this I create the table dbo.DeadlockEvents in the DBAEventManagement database.

-- Check Database Exists
IF  NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'DBAEventManagement')
      CREATE DATABASE DBAEventManagement ;
GO

USE DBAEventManagement ;
GO

-- Create table to hold deadlock graphs
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeadlockEvents]') AND type in (N'U'))
      DROP TABLE [dbo].[DeadlockEvents]
GO

CREATE TABLE [dbo].[DeadlockEvents](
      [Id] [integer] NOT NULL IDENTITY,
      [AlertTime] [datetime2] NOT NULL CONSTRAINT DF_AlertTime DEFAULT SYSDATETIME(),
      [DeadlockGraph] [xml] NULL
)
GO

It is important that you enable the option to “Replace tokens for all job responses to alerts” on the Alert System tab of the SQL Server Agent Properties (right click SQL Server Agent in SSMS and choose properties). Doing this has certain security implications as it is a system setting and any windows user with write permissions on the Windows Event Log can access job steps that are activated by SQL Server Agent alerts or WMI alerts when it is enabled. See the topic Using “Tokens in Job Steps” in Books Online for more.



Make sure this option is checked for WMI Alerts to work.

The next part of creating the monitoring system is to create a job to service the event notifications. You can either do this using a script or through SQL Server Management Studio. I have created a job called “Capture Deadlock Graph”



With a job step Insert graph into DeadlockEvents, that executes a T-SQL INSERT statement to extract the TextData property from the WMI alert.

INSERT INTO DeadlockEvents(DeadlockGraph)
VALUES (N'$(ESCAPE_SQUOTE(WMI(TextData)))')

Any single quotes within the token is escaped to avoid syntax issues.



Once the job has been created you can create the alert. You will find a script to do this here or to use SSMS. To create the alert  right click the Alerts tab under SQL Server Agent and choose to create a New Alert. I’ve called my alert “Respond to DEADLOCK_GRAPH”, the type should be set to WMI event alert. Leave the namespace as \\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER and enter the WQL Query

SELECT * FROM DEADLOCK_GRAPH



The final thing is to setup the Job as the response to the Alert being fired. To do this choose the Response tab. Click the Execute job checkbox and select your job from the drop down. You can set up a Notification so that you are emailed, paged or have a network message when this alert fires. This will allow you to be pro-active in monitoring your system.

Now everything is in place you can test it using the deadly embrace scripts I posted in the Event Notification blog post Deadlock_Process_1.sql and Deadlock_Process_2.sql.

Be aware that there may be a delay between the event happening and it being registered. You can check the Alert History under the alert’s properties to see if it fired. The number of occurrences will be incremented when the alert fires.



If the event has fired check  the job history for the “Capture Deadlock Graph” job. If that has worked you should have a new row in the dbo.DeadlockEvents table. If you output your query to a grid you can then open the XML in a new window.



If you look at the XML generated, you will notice that the deadlock-list element is included in the TextData element. Similar to the event notification you will need to strip out this element if you wish to create a deadlock graph file that can be opened my SSMS. e.g. to get the most recent deadlock graph use the query:

SELECT TOP 1 [DeadlockGraph].query('/TextData/deadlock-list')
FROM [DBAEventManagement].[dbo].[DeadlockEvents]
ORDER BY Id DESC
GO

To create an XML file use this query in a BCP command e.g.

BCP "SELECT TOP 1 [DeadlockGraph].query('/TextData/deadlock-list') FROM [DBAEventManagement].[dbo].[DeadlockEvents] ORDER BY Id DESC" queryout xmlfile.xdl -T -c –q

This will create a file xmlfile.xdl in the current directory, which can then be opened in SQL Server Management Studio.



A script to create the job and the  alert can be found here.

WMI Alerts may be the first steps you can take to be pro-active in monitoring your SQL Server 2005 and SQL Server 2008 instances.