The data you need is recorded in the default extended events trace.
DECLARE @xml XML
SELECT @xml = target_data
ON event_session_address = address
WHERE name = 'system_health'
AND target_name = 'ring_buffer'
XEventData.XEvent.query('(data/value/deadlock)') AS DeadlockGraph,
CAST(XEventData.XEvent.value('(data/value)', 'varchar(max)') AS XML) AS DeadlockGraph,
XEventData.XEvent.value('(./@timestamp)', 'DATETIME2') AS [DateTime]
FROM (SELECT @xml AS TargetData) AS Data
TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)
ORDER BY [DateTime] DESC
Though it won't be there any more if you have restarted the service -e.g. to apply a trace flag or if the buffer has cycled in the meantime.
You can set up your own extended events trace that stores the deadlock graph to a file target for persistent non volatile storage. Example Code here. I personally find the deadlock graph XML more friendly than the trace flag output.
- @MartinC points out in the comments that on instances of SQL Server that don't have all the updates there might be a problem with it generating invalid XML. The fix for that is to do some search and replace and use
CAST(REPLACE(REPLACE(XEventData.XEvent.value('(data/value)', 'varchar(max)'), '<victim-list>', '<deadlock><victim-list>'), '<process-list>', '</victim-list><process-list>') AS XML) AS DeadlockGraph in the
SELECT list as described here.
- Wayne Sheffield has posted a useful script to shred the deadlock graph XML into tabular format here.