We were dealing with some deadlock problems in our database. However, unfortunately we did not have trace flag 1204 and/or trace flag 1222 turned on. Fortunately there is a way to retroactively retrieved deadlock information starting with SQL Server 2008 using Extended Events.
Someone forwarded an article written by Jonathan Kehayias (blog | twitter), an expert in Extended Events, regarding this. The article can be found here. It is a great article. In the article, Jonathan showed us the way to retrieved the deadlock graph from Extended Events.
Jonathan also mentioned in the article that there is a bug in the output of deadlock graph from the Extended Events, in which it is not a valid XML. There is a problem with missing end tag for the <victim-list> node. He then provided a work-around script in which he utilizes the replace function on the deadlock information text from the Extended Events. Then after that we could cast the text as XML.
I went ahead and try that script. When I tried it, I got the following error message:
Msg 9436, Level 16, State 1, Line 1
XML parsing: line 5, character 17, end tag does not match starting
Upon further review, it looks like the script works on deadlocks that have exactly 1 <victimProcess> node. However, if there is empty <victimProcess> node or multiple <victimProcess> nodes, the script would not work. In the case of multiple <victimProcess> nodes, for some reasons, the Extended Events only closed the last <victimProcess> node, but all the other ones are missing the end tag. Thus, I made a small tweak to Jonathan’s script to handle empty <victimProcess> node or multiple <victimProcess> nodes. Here’s the script:
SELECT CAST(REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'),
'<victimProcess', '</victimProcess><victimProcess'),
'<victim-list>', '<deadlock><victim-list><victimProcess>'),
'<process-list>', '</victim-list><process-list>'),
'<victim-list/>', '<deadlock><victim-list>'),
'<victimProcess>' + CHAR(10) + SPACE(2) + '</victimProcess>', '') AS XML) 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'
