Troubleshooting Using Ring Buffers

Article
04/09/2018

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 :-

[sql]SELECT DISTINCT ring_buffer_type FROM sys.dm_os_ring_buffers[/sql]

Which gives me:

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:

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:

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

Resulting in:

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:

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

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

Troubleshooting Using Ring Buffers

Additional resources