Deadlock error isn't returning the deadlock SQL

Question

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.

Answer 1

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

1. @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 the SELECT list as described here.
2. Wayne Sheffield has posted a useful script to shred the deadlock graph XML into tabular format here.

Answer 2

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.