I am running the query below and seeing deadlocks from 2013-03-01 17:27:44.213 to 2013-03-03 06:51:22.317. It is currently 2013-03-04 15:20. You may say "Well then, there are no deadlocks after 6:51!" I wuold say that is true, except we record them in the error log and I know there are plenty. Also... If I wait a couple of minutes and run my query again, I will get deadlocks from 2013-03-01 17:30:42.153 to 2013-03-03 06:54:51.211. What is going on? Am I only able to pick up the first half (or some percent) of the ring buffer?
select XEventData.XEvent.value('@timestamp', 'datetime') AS DeadlockDateTime,
CONVERT( xml, 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 WITH(NOLOCK)
join sys.dm_xe_sessions s WITH(NOLOCK) 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'
PFE - MicrosoftSQL Know-It-AllHow to post data/code on a forum to get the best help - Jeff Moden