The days of restarting SQL Service with trace flags 1204 or 1222 (MSDN Detecting and Ending Deadlocks) or being forced to start up a profiler trace are no more.
Let’s face it. There are times when someone comes up to you and says, “hey we got a handful of deadlocks an hour ago can you tell me how it happened?” Now, if you are practicing pro-active DBA methods then you might already have this information but if you are left with the deer in the head-lights look then these queries might be for you.
The comments on the queries below are fairly self explanatory. Running the first query will return a single record with a lot of improperly formatted xml. This query is given because it is very fast. All you need to do then is paste it into notepad and search for xml_deadlock_report to find the deadlock graph xml. I provided the bottom query for use when you come across the sql handles in the xml to identify the queries that were being run.
The middle query is the best of the bunch here but can be very slow. This one will return a row per deadlock graph in properly formatted xml. Must easier to work with.
In both cases the most recent deadlock events will be at the bottom of the result set or the bottom of the xml code block copied out from query one.
-- This query is fast but the xml is not formatted properly so you -- need to paste the result in notepad and search for xml_deadlock_report. 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' -- This query gives you a record per deadlock graph with xml corrected -- but this query is VERY slow. There is no server performance impact -- but you should expect this query to run for 30 mins + select XEventData.XEvent.value('(data/value)', 'varchar(max)') 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' /*For viewing the sql handles*/ SELECT * FROM sys.dm_exec_sql_text(0x03001a00ea93ed5bed752b0150a100000100000000000000);
Credits: (edited – 2013-02-13)