Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Querying the ringbuffer target - only returning the first half of the data Expand / Collapse
Author
Message
Posted Monday, March 4, 2013 2:25 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:04 PM
Points: 2,702, Visits: 3,411
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)[1]', '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'
OPTION(MAXDOP 1)



Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1426466
Posted Tuesday, March 5, 2013 5:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:04 PM
Points: 2,702, Visits: 3,411
I saw that there was a bug about if the ring buffer was greater than 4mb, but we are on 200 R2 SP1 CU6... Is this still an existing issue?

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1426770
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse