hello mahesh (9/17/2012)
We had couple of deadlock issues,
but nothing is captured with this query..
First try this:
WITH SessionData as (
XEventData.XEvent.value('(data/value)', 'varchar(max)') VarcharResults
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[@name="xml_deadlock_report"]') AS XEventData (XEvent)
substring(VarcharResults, charindex( 'lasttranstarted="', VarcharResults) + len('lasttranstarted="'),19) ApproxDateGuess
, cast (
-- bad tag, should be <victimProcess id="process_______"/>
when patindex( '%<victimProcess id="process_______">%', VarcharResults ) > 1 then
stuff( VarcharResults , patindex( '%<victimProcess id="process_______">%', VarcharResults )+34,0,'/')
, '<victim-list>', '<deadlock><victim-list>'), '<process-list>','</victim-list><process-list>')
as XML ) DeadlockTree
ORDER BY 1;
if that doesn't work, I can think of two things--
1) If you have a lot of extended events, the ring buffer may have lapped itself
2) I've seen cases where it appears like an event with bad XML gets stuck in the ring buffer and you can't see past it until the bad event gets over written.