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

• Jared

  SSC Guru

  Points: 61793

  More actions

  March 4, 2013 at 2:25 pm

  #271749

  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

• Jared

  SSC Guru

  Points: 61793

  More actions

  March 5, 2013 at 5:45 am

  #1593428

  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