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 …
So the ring buffers available are :-
SELECT DISTINCT ring_buffer_type FROM sys.dm_os_ring_buffers
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.
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:
SELECT<br> DATEADD(ms, -1 * (info.ms_ticks - XEvent.value('(@time)', 'INT')), GETDATE()) AS [TimeStamp]<br> , XEvent.value('(@id)', 'INT') AS [ID]<br> , XEvent.value('(./Exception/Error)', 'INT') AS [Error]<br> , XEvent.value('(./Exception/Severity)', 'INT') AS [Severity]<br> , XEvent.value('(./Exception/State)', 'INT') AS [State]<br> , XEvent.value('(./Exception/UserDefined)', '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)', 'INT')<br> CROSS APPLY sys.dm_os_sys_info info
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 <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.