Event Notifications

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.