SQL Server – Troubleshoot connectivity issues with Connectivity Ring Buffer

08 August,2012 by Jack Vamvas

SQL Server Ring Buffers , such as RING_BUFFER_CONNECTIVITY and RING_BUFFER_CONNECTIVITY are very useful for troubleshooting certain SQL Server Connectivity Issues. They are very fast and stored on the fly – which makes them easy to use.

The following error appeared in the SQL Server Error Logs on the daily Reporting of Powershell and SQL Server Error Logs

A fatal error occurred while reading the input stream from the network. The session will be terminated (input error: 64, output error: 0).

Error: 4014, Severity: 20, State: 11.

Based on a Connect Item  article - these are potentially TDS based errors.

It’s recommended to use the connectivity ring buffer to find out which client triggered the 4014 error.

What does the 'RING_BUFFER_CONNECTIVITY' offer ?

SELECT  CAST(record AS XML)
FROM    sys.dm_os_ring_buffers
WHERE   ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'

 

 The RING_BUFFER_CONNECTIVITY returns records for :

a)  A server-initiated connection closure which includes Login Failed Event and session kill. You will also see connectivity issues where there is no record- this means the cleint has reset .

b)  A circular logging of 1000 records is maintained

c) The record types returned are: Connection Close, Error,Login Timers

Example 1  is a straightforward example of a KILL <SPID> . Some highlights:

1) RecordType = ConnectionClose

2) Spid = 892

3) RemoteHost = 10.110.30.182

4) TdsInputBufferBytes = 116

5) SessionIsKilled = 1

6) The <frame> xml tags displays the function hexadecimal address

Example 1

 

<Record id="1862039" type="RING_BUFFER_CONNECTIVITY" time="715373666">

  <ConnectivityTraceRecord>

    <RecordType>ConnectionClose</RecordType>

    <RecordSource>Tds</RecordSource>

    <Spid>892</Spid>

    <SniConnectionId>B2E2926A-8023-4A20-A995-D3DB3D8448A2</SniConnectionId>

    <SniProvider>7</SniProvider>

    <RemoteHost>10.110.30.182</RemoteHost>

    <RemotePort>2421</RemotePort>

    <LocalHost>10.120.140.3</LocalHost>

    <LocalPort>57325</LocalPort>

    <RecordTime>8/8/2012 10:58:57.309</RecordTime>

    <TdsBuffersInformation>

      <TdsInputBufferError>0</TdsInputBufferError>

      <TdsOutputBufferError>0</TdsOutputBufferError>

      <TdsInputBufferBytes>116</TdsInputBufferBytes>

    </TdsBuffersInformation>

    <TdsDisconnectFlags>

      <PhysicalConnectionIsKilled>0</PhysicalConnectionIsKilled>

      <DisconnectDueToReadError>0</DisconnectDueToReadError>

      <NetworkErrorFoundInInputStream>0</NetworkErrorFoundInInputStream>

      <ErrorFoundBeforeLogin>0</ErrorFoundBeforeLogin>

      <SessionIsKilled>1</SessionIsKilled>

      <NormalDisconnect>0</NormalDisconnect>

      <NormalLogout>0</NormalLogout>

    </TdsDisconnectFlags>

  </ConnectivityTraceRecord>

  <Stack>

    <frame id="0">0X00000000021B6CF3</frame>

    <frame id="1">0X00000000021B6E55</frame>

    <frame id="2">0X000000000250CE76</frame>

    </Stack>

</Record>

Example 2

<Record id="1883519" type="RING_BUFFER_CONNECTIVITY" time="718908235">

  <ConnectivityTraceRecord>

    <RecordType>Error</RecordType>

    <RecordSource>TdsBySniConnection</RecordSource>

    <Spid>0</Spid>

    <SniConnectionId>00000000-0000-0000-0000-000000000000</SniConnectionId>

    <OSError>0</OSError>

    <SniConsumerError>4002</SniConsumerError>

    <SniProvider>10</SniProvider>

    <State>2</State>

    <RemoteHost />

    <RemotePort>0</RemotePort>

    <LocalHost />

    <LocalPort>0</LocalPort>

    <RecordTime>8/8/2012 11:57:51.484</RecordTime>

  </ConnectivityTraceRecord>

  <Stack>

    <frame id="0">0X00000000021B6CF3</frame>

    <frame id="1">0X00000000021D568D</frame>

    <frame id="2">0X0000000002245EB7</frame>

  </Stack>

</Record>

 

I checked the 'RING_BUFFER_EXCEPTION' for related and more detailed information . Where there is details about: Error, Severity,State. I was able to work with the developers in resolving the issue

 

SELECT  CAST(record AS XML)
FROM    sys.dm_os_ring_buffers
WHERE   ring_buffer_type = 'RING_BUFFER_EXCEPTION'

 

<Record id="2069510" type="RING_BUFFER_EXCEPTION" time="723097453">

  <Exception>

    <Task address="0x0000000009813948" />

    <Error>4002</Error>

    <Severity>16</Severity>

    <State>2</State>

    <UserDefined>0</UserDefined>

  </Exception>

  <Stack>

    <frame id="0">0X0000000000DE84B9</frame>

    <frame id="1">0X0000000000DE8B01</frame>

    <frame id="2">0X0000000002245EC6</frame>

    <frame id="3">0X0000000002245F77</frame>

    <frame id="4">0X000000000224CA6E</frame>

    <frame id="5">0X00000000027B81EE</frame>

    <frame id="6">0X0000000002D73516</frame>

    <frame id="7">0X0000000002D73644</frame>

    <frame id="8">0X0000000002D9AB75</frame>

    <frame id="9">0X0000000002DD4145</frame>

    <frame id="10">0X0000000000996251</frame>

    <frame id="11">0X00000000009932C5</frame>

    <frame id="12">0X000000000099337E</frame>

    <frame id="13">0X00000000009256CA</frame>

    <frame id="14">0X00000000009EBE13</frame>

    <frame id="15">0X00000000009A84C5</frame>

  </Stack>

</Record>

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server – Troubleshoot connectivity issues with Connectivity Ring Buffer


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer