Did you know that SQL Server 2008 has the ability to retrieve deadlock information after the fact without having previously enabled any additional tracing? It’s part of the new advanced troubleshooting feature called Extended Events. Here’s a SQL query from Jonathan Kehayias which retrieves SQL Server 2008 deadlock information:
select CAST( REPLACE( REPLACE(XEventData.XEvent.value(‘(data/value)[1]’, ‘varchar(max)’), ”, ”), ”,”) 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’
More info. here: Using SQL Server 2008 Extended Events