hello mahesh (9/17/2012)
We had couple of deadlock issues,but nothing is captured with this query..
select XEventData.XEvent.value('(data/value)[1]', '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'
We had the same issues here as hello mahesh...
select @@version
Microsoft SQL Server 2008 (SP3) - 10.0.5512.0 (X64) Aug 22 2012 19:25:47 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)
I found that the issue was with this line:
cross apply TargetData.nodes ('//RingBufferTarget/event') as XEventData (XEvent)
Using either one of the following two lines returned the expected results:
cross apply TargetData.nodes ('/RingBufferTarget/event') as XEventData (XEvent)
cross apply TargetData.nodes ('RingBufferTarget/event') as XEventData (XEvent)
Hope this helps.