Any time I am running any of the suggested code to grab the deadlock graphs, I am looking at the xml and seeing that object_id in the resource-list is blank. Is it supposed to be this way? The reason I ask is because I found another snippet of code that parses the graph out to log the essential information to a table.

1. Script to produce graph:

select CONVERT( xml, XEventData.XEvent.value('(data/value)[1]', 'varchar(max)')) as DeadlockGraph

FROM (select CAST(target_data as xml) as TargetData

from sys.dm_xe_session_targets st

join sys.dm_xe_sessions s on s.address = st.event_session_address

where name = 'system_health') AS Data

CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)

where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

OPTION(MAXDOP 1) I use the MAXDOP because parallelism makes my execution time huge the first time I run this.

2. Add in my parsing script:

;WITH log_deadlocks

AS (

select CONVERT( xml, XEventData.XEvent.value('(data/value)[1]', 'varchar(max)')) as DeadlockGraph

FROM (select CAST(target_data as xml) as TargetData

from sys.dm_xe_session_targets st

join sys.dm_xe_sessions s on s.address = st.event_session_address

where name = 'system_health') AS Data

CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)

where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

)

Select

--DeadlockDateTime,

DeadlockGraph,

DbName = DB_NAME(ResList.KeyLock.value('@dbid', 'INT')),

ObjectName = ResList.KeyLock.value('@objectname', 'sysname'),

IndexName = ResList.KeyLock.value('@indexname', 'sysname'),

ResultingLockMode = ResList.KeyLock.value('@mode', 'varchar(10)'),

OwnerID = OwnList.Own.value('@id', 'sysname'),

OwnerLockMode = OwnList.Own.value('@mode', 'varchar(10)'),

WaiterID = WaitList.Wait.value('@id', 'sysname'),

WaiterLockMode = WaitList.Wait.value('@mode', 'varchar(10)')

From Log_Deadlocks

-- Shred keylock node of the resource-list

Cross Apply DeadlockGraph.nodes('//resource-list/keylock') ResList(KeyLock)

-- Shred the owner node beneath the resource-list/keylock node

Cross Apply ResList.KeyLock.nodes('./owner-list/owner') OwnList(Own)

-- Shred the waiter node beneath the resource-list/keylock node

Cross Apply ResList.KeyLock.nodes('./waiter-list/waiter') WaitList(Wait);

When I look at Jonathan's Extended Event Session Explorer (We are on 2008 R2) I DO see object names for the deadlocks. Any thoughts?