Event Notifications

Published 22 May 09 10:15 AM | MartinBell

At the last Manchester SQL Server User Group one of Tony Rogerson’s tips was to enable event notifications for blocked processes. He has an excellent blog posting about it here, This is very similar to the WMI alerts that I have used for monitoring and storing deadlock graphs.

You can find out what events can be used by querying the sys.event_notification_event_types table. In the list you will see DEADLOCK_GRAPH which is the event required to give the XML for the deadlock graph. So creating the event notification will be:

CREATE EVENT NOTIFICATION notify_locks
    ON SERVER
    WITH fan_in
    FOR DEADLOCK_GRAPH
    TO SERVICE 'syseventservice', 'current database' ;
GO

An easy way to test for deadlocks is to create your own deadly embrace queries such as Deadlock_Process_1.sql and Deadlock_Process_2.sql. Open both of the files in Management studio and start them running in quick sucession.

Looking in the event queue:

SELECT cast( message_body as xml ), *
FROM syseventqueue ;
GO

Will show a message there, although the saving this XML as it is to a file will not create a valid deadlock graph (xdl) file. To do that you need to only extract the deadlock list.

SELECT cast( message_body as xml ).query('EVENT_INSTANCE/TextData/deadlock-list')
FROM syseventqueue ;
GO

The script I use to set up the notification can be found here. I’ll talk about setting up WMI Alerts in a future post.

Comments

# Martin Bell UK SQL Server MVP said on May 26, 2009 08:50 AM:

WMI Event Alerts can be the first steps to creating a pro-active monitoring system for you SQL Servers. Here is how you can capture deadlock graphs using them.

# 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

# Tony Rogerson's ramblings on SQL Server said on October 8, 2009 08:21 PM:

See attached for the deck and scripts, any questions or you want to talk further just email me @ tonyrogerson

This Blog

SQL Blogs

Syndication