Deadlock error isn't returning the deadlock SQL

Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I am getting this error randomly when one of my websites gets busy. I know roughly which sets of tables it is happening on but in my experience with other programs I normally get the SQL returned where the deadlock is happening. Is there a flag I should turn on to allow this to happen?

I will try and debug the deadlock itself as a seperate issue as this is my main question for now.

I am using SQL Server 2008 Standard Edition.

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)'), '', ''), '', '') 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.

StackExchange Database Administrators Q#10644, answer score: 26