Troubleshooting Using Ring Buffers



Working at a customer site, and I happened to notice that the Exception ring buffer contained a lot (and I mean A.L.O.T.) of errors. Ring Buffers were introduced in SQL 2008, and contain a fixed size cycle of records for the ring buffer they pertain to. Now… note – sys.dm_os_ring_buffers is a DMV which is NOT SUPPORTED. and future compatibility is NOT guaranteed… But… that doesn’t mean we can’t use them – right Winking smile

So the ring buffers available are :-

SELECT DISTINCT ring_buffer_type FROM sys.dm_os_ring_buffers

Which gives me:

image

For this Blog Post, I am particularly interested in the RING_BUFFER_EXCEPTION ring buffer, as this will show me the exceptions which have been occurring across the instance. Since these exceptions might only be a severity of 15 or below, it is entirely possible that we don’t know they are occurring – unless we look in the ring buffer.

But how?

If I cast the ring buffer record as an XML datatype, I can see the format of the payload:

image

So, I really need to break this XML down so I can do something useful with it, and whilst I am at it, I can use the sys.messages table to turn the Error (<Error>926</Error> in this case) into something slightly more meaningful, with:

SELECT<br>
      DATEADD(ms, -1 * (info.ms_ticks - XEvent.value('(@time)[1]', 'INT')), GETDATE()) AS [TimeStamp]<br>
    , XEvent.value('(@id)[1]', 'INT') AS [ID]<br>
    , XEvent.value('(./Exception/Error)[1]', 'INT') AS [Error]<br>
    , XEvent.value('(./Exception/Severity)[1]', 'INT') AS [Severity]<br>
    , XEvent.value('(./Exception/State)[1]', 'INT') AS [State]<br>
    , XEvent.value('(./Exception/UserDefined)[1]', 'INT') AS [UserDefined]<br>
    , sm.text<br>
FROM (<br>
       SELECT<br>
           CAST(record AS XML) AS [target_data]<br>
       FROM<br>
           sys.dm_os_ring_buffers<br>
       WHERE<br>
           ring_buffer_type = 'RING_BUFFER_EXCEPTION' ) as XEventsRingBuffer<br>
CROSS APPLY target_Data.nodes('/Record') AS XEventData (XEvent)<br>
INNER JOIN sys.messages sm<br>
ON sm.message_id = XEvent.value('(./Exception/Error)[1]', 'INT')<br>
CROSS APPLY sys.dm_os_sys_info info

Resulting in:

image

Ok – that’s cool. So I can now use the ring buffer to extract the XML of the exception and turn it into something a bit more human readable.

What if I need to investigate further? What if the Exception leads me to something not so straight-forward? Well, XEvents to the rescue:

-- If the Event Sessions already Exists, drop it<br>
IF EXISTS ( SELECT 1 FROM [sys].[server_event_sessions] WHERE [name] = N'XE_Error_Capture')<br>
BEGIN<br>
    DROP EVENT SESSION [XE_Error_Capture] ON SERVER;<br>
END<br>
-- Create the XEvent to catch the error<br>
CREATE EVENT SESSION [XE_Error_Capture]<br>
ON SERVER<br>
ADD EVENT [sqlserver].[error_reported] (<br>
    ACTION([sqlserver].[session_id],<br>
           [sqlserver].[database_name],<br>
           [sqlserver].[tsql_stack])<br>
WHERE ([error_number]=123456789)) -- Replace with Error Number&nbsp;&nbsp; <br>
ADD TARGET [package0].[event_file](SET FILENAME = N'C:\Temp\XE_Error_Capture.xel')<br>
GO<br>
-- Start the Session<br>
ALTER EVENT SESSION [XE_Error_Capture] ON SERVER<br>
STATE = START;<br>
GO


I can now use an XEvent to specifically hunt out the Errors previously discovered in the ring buffer – including the database which they are occurring in, and the tsql_stack (should I need it). This can be broken down in the usual manner, or even viewed directly with the XEvent Viewer built right into SSMS.

Neat Smile


Comments (1)

  1. Oren Bouni says:

    Grate Post was very helpfull

Skip to main content