Querying the ringbuffer target - only returning the first half of the data

  • 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)

    Jared
    CE - Microsoft

  • 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?

    Jared
    CE - Microsoft

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply