WMI Event Alerts

Published 26 May 09 10:30 AM | MartinBell

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.

Agent Properties

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

Token Replacement

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”

Deadlokc Graph Job

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.

Deadlock Graph Job Step

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

Adding WMI Alert

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.

Alert History

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.

WMI Alert XML

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.

Deadlock Graph

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.

Comments

# Dew Drop - May 26, 2009 | Alvin Ashcraft's Morning Dew said on May 26, 2009 01:06 PM:

Pingback from  Dew Drop - May 26, 2009 | Alvin Ashcraft's Morning Dew

# Tony Rogerson's ramblings on SQL Server said on September 30, 2009 09:56 PM:

Note that I will be going into more detail in a series of 30-40 minute LiveMeetings starting towards

# Martin Bell UK SQL Server MVP said on November 3, 2010 09:45 PM:

if you are processing deadlock graphs from Extended Events then you may want to see these ways to store the information gathered before it is lost!

# Event Xml | More More Pics said on February 19, 2011 09:02 AM:

Pingback from  Event Xml | More More Pics

This Blog

SQL Blogs

Syndication