Using sys.dm_os_ring_buffers to Troubleshoot Connectivity Issues

I encountered an interesting question over on the MSDN forums concerning a poster that was reporting experiencing an issue whereby the “sa” account kept being locked out.

In scenarios such as this the SQL Server Error Log is your friend, as it can be configured to record failed login attempts for an instance. Of course pursuing that avenue of exploration would make for a rather dull blog post and so instead we’re going to take a look at using the lesser known DMV sys.dm_os_ring_buffers.

Note that you use this particular DMV at your own peril considering Books Online states that:

“The following SQL Server Operating System–related dynamic management views are Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.”

In other words don’t go making use of this DMV as an integral part of your monitoring solution but rather be mindful of it’s existence as a possible tool for ad-hoc use.

Sounds like just the sort of thing we should look into. The DMV sys.dm_os_ring_buffers maintains approximately 1000 records, before wrapping around and replacing the oldest entries first. It exposes four columns but we’re primarily only interested in one of them, record, which contains XML data. Yippee! We’ll also filter the results to include just the connectivity ring buffer data.

SELECT CAST(record as xml) AS record_data
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type= 'RING_BUFFER_CONNECTIVITY'

Executing the statement above will produce output similar to the following:

Clicking an XML fragment hyperlink will open the contents into a more readable format in a new tab in SSMS for you similar to below, showing a basic ring buffer connectivity error record.

<Record id="5577586" type="RING_BUFFER_CONNECTIVITY" time="9300997329">
  <ConnectivityTraceRecord>
    <RecordType>Error</RecordType>
    <RecordSource>Tds</RecordSource>
    <Spid>80</Spid>
    <SniConnectionId>4BBB38FE-B46A-4D6E-917F-5D473464448B</SniConnectionId>
    <OSError>0</OSError>
    <SniConsumerError>18456</SniConsumerError>
    <SniProvider>7</SniProvider>
    <State>8</State>
    <RemoteHost>10.323.12.32</RemoteHost>
    <RemotePort>1234</RemotePort>
    <LocalHost>10.132.321.32</LocalHost>
    <LocalPort>1888</LocalPort>
    <RecordTime>2/18/2014 14:2:58.588</RecordTime>
    <TdsBuffersInformation>
      <TdsInputBufferError>0</TdsInputBufferError>
      <TdsOutputBufferError>0</TdsOutputBufferError>
      <TdsInputBufferBytes>320</TdsInputBufferBytes>
    </TdsBuffersInformation>
    <TdsDisconnectFlags>
      <PhysicalConnectionIsKilled>0</PhysicalConnectionIsKilled>
      <DisconnectDueToReadError>0</DisconnectDueToReadError>
      <NetworkErrorFoundInInputStream>0</NetworkErrorFoundInInputStream>
      <ErrorFoundBeforeLogin>0</ErrorFoundBeforeLogin>
      <SessionIsKilled>0</SessionIsKilled>
      <NormalDisconnect>0</NormalDisconnect>
    </TdsDisconnectFlags>
  </ConnectivityTraceRecord>
  <Stack>
    <frame id="0">0X0000000001D6C34B</frame>
    <frame id="1">0X0000000001D68FDD</frame>
    <frame id="2">0X0000000002A81001</frame>
    <frame id="3">0X0000000001267E98</frame>
    <frame id="4">0X0000000000E215AD</frame>
    <frame id="5">0X0000000000E21492</frame>
    <frame id="6">0X00000000009EBBD8</frame>
    <frame id="7">0X00000000009EB8BA</frame>
    <frame id="8">0X00000000009EB6FF</frame>
    <frame id="9">0X0000000000F08FB6</frame>
    <frame id="10">0X0000000000F09175</frame>
    <frame id="11">0X0000000000F09839</frame>
    <frame id="12">0X0000000000F09502</frame>
    <frame id="13">0X00000000757C37D7</frame>
    <frame id="14">0X00000000757C3894</frame>
    <frame id="15">0X000000007796652D</frame>
  </Stack>
</Record>

As you can see there’s some rather useful information contained within the XML document. Things such as SniConsumerError, State and the RemoteHost responsible for the connection.

The RecordType node is particularly relevant for the troubleshooting case our forum friend had, with a node value of “Error”, indicating a connection error naturally. By identifying a connectivity ring buffer record of this type, we can then look-up the SniConsumerError code to establish precisely what error was encountered.

Other RecordType‘s include LoginTimers (handy for troubleshooting connection timeouts) and ConnectionClose (can be used to identify killed Spids).

Great you say but a busy DBA does not want to have to click through hoards of XML documents in order to find possible information of interest. Let’s get shredding then:

;WITH RingBufferConnectivity as
(SELECT
records.record.value('(/Record/@id)[1]', 'int') AS [RecordID],
records.record.value('(/Record/ConnectivityTraceRecord/RecordType)[1]', 'varchar(max)') AS [RecordType],
records.record.value('(/Record/ConnectivityTraceRecord/RecordTime)[1]', 'datetime') AS [RecordTime],
records.record.value('(/Record/ConnectivityTraceRecord/SniConsumerError)[1]', 'int') AS [Error],
records.record.value('(/Record/ConnectivityTraceRecord/State)[1]', 'int') AS [State],
records.record.value('(/Record/ConnectivityTraceRecord/Spid)[1]', 'int') AS [Spid],
records.record.value('(/Record/ConnectivityTraceRecord/RemoteHost)[1]', 'varchar(max)') AS [RemoteHost],
records.record.value('(/Record/ConnectivityTraceRecord/RemotePort)[1]', 'varchar(max)') AS [RemotePort],
records.record.value('(/Record/ConnectivityTraceRecord/LocalHost)[1]', 'varchar(max)') AS [LocalHost]
FROM
(SELECT CAST(record as xml) AS record_data
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type= 'RING_BUFFER_CONNECTIVITY'
) TabA
CROSS APPLY record_data.nodes('//Record') AS records (record)
)
SELECT RBC.*, m.text
FROM RingBufferConnectivity RBC
LEFT JOIN sys.messages M ON
RBC.Error = M.message_id AND M.language_id = 1033
WHERE RBC.RecordType='Error' --Comment Out to see all RecordTypes
ORDER BY RBC.RecordTime DESC

Executing the query above produces a more readable result set such as the one below. You can see that in the query we joined our ring buffer data to the sys.messages catalog view in order to grab the text for the Error id. Splendid. Using this information we track down the precise source responsible for the Error: Login failed.

(Note: Sensitive information has been omitted from results above, RemoteHost etc.)

Take a look at the DMV sys.dm_os_ring_buffers and keep it in mind as a potential troubleshooting aid for the next time you have a connectivity issue to diagnose.

Further reading: Connectivity troubleshooting with the Connectivity Ring Buffer

Credit: Photo by localstatic used under Creative Commons