Deadlock error isn't returning the deadlock SQL
The data you need is recorded in the default extended events trace.
DECLARE @xml XML
SELECT @xml = target_data
FROM sys.dm_xe_session_targets
JOIN sys.dm_xe_sessions
ON event_session_address = address
WHERE name = 'system_health'
AND target_name = 'ring_buffer'
SELECT
XEventData.XEvent.query('(data/value/deadlock)[1]') AS DeadlockGraph,
CAST(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') AS XML) AS DeadlockGraph,
XEventData.XEvent.value('(./@timestamp)[1]', 'DATETIME2') AS [DateTime]
FROM (SELECT @xml AS TargetData) AS Data
CROSS APPLY
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.
Edit
- @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)[1]', 'varchar(max)'), '<victim-list>', '<deadlock><victim-list>'), '<process-list>', '</victim-list><process-list>') AS XML) AS DeadlockGraph
in theSELECT
list as described here. - Wayne Sheffield has posted a useful script to shred the deadlock graph XML into tabular format here.
The accepted answer did not work for me consistently. The ring buffer apparently is known to drop events in certain circumstances..
ConnectItem
Ring Buffer Issues
The system_health log event files can parsed (from this answer):
with XmlDeadlockReports as
(
select convert(xml, event_data) as EventData
from sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
where substring(event_data, 1, 50) like '%"xml_deadlock_report"%'
)
select EventData.value('(event/@timestamp)[1]', 'datetime2(7)') as TimeStamp,
EventData.query('event/data/value/deadlock') as XdlFile
from XmlDeadlockReports
order by TimeStamp desc
The XdlFile field can be saved to an .xdl file and read into SSMS. Tested in Sql Server 2012.