Troubleshooting connectivity problems in SQL Server 2008 with the Connectivity Ring Buffer

By admin

Few days ago we started having problems with our system based on PHP 5 and MSSQL 2008. The problem is intermittent and it doesn’t seem to be any logical explanation for it but this is what happens:

Few times a day when using our PHP system we get the following error:

Warning: mssql_connect() [function.mssql-connect]: Unable to connect
to server: 192.168.0.22 in D:\xampp\htdocs\_PROD\BE\_config
\database.connection.php on line 7
Cannot Connect To Server

In most cases, refreshing the page helps, and problem goes away. In order to to troubleshoot this problem with SQL Server 2008 we can use the new functionality that hasn’t been available in former versions of SQL Server called Connectivity Ring Buffer.

In order to do this, open a new query window and run the following command:
SELECT CAST(record AS XML) FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = ‘RING_BUFFER_CONNECTIVITY’

In my case it returns two records:

<Record id="0" type="RING_BUFFER_CONNECTIVITY" time="18230162">
 <ConnectivityTraceRecord>
  <RecordType>Error</RecordType>
  <RecordSource>Tds</RecordSource>
  <Spid>51</Spid>
  <SniConnectionId>55D5EE76-3898-4D75-94B9-58CC5E90E4D5</SniConnectionId>
  <OSError>0</OSError>
  <SniConsumerError>18456</SniConsumerError>
  <SniProvider>4</SniProvider>
  <State>38</State>
  <RemoteHost>&lt;local machine&gt;</RemoteHost>
  <RemotePort>0</RemotePort>
  <LocalHost />
  <LocalPort>0</LocalPort>
  <RecordTime>5/17/2011 20:39:34.921</RecordTime>
  <TdsBuffersInformation>
   <TdsInputBufferError>0</TdsInputBufferError>
   <TdsOutputBufferError>0</TdsOutputBufferError>
   <TdsInputBufferBytes>96</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">0X00000000013CC34B</frame>
  <frame id="1">0X00000000013C8FDD</frame>
  <frame id="2">0X00000000020E1001</frame>
  <frame id="3">0X00000000008C7E98</frame>
  <frame id="4">0X00000000004815AD</frame>
  <frame id="5">0X0000000000481492</frame>
  <frame id="6">0X000000000004BBD8</frame>
  <frame id="7">0X000000000004B8BA</frame>
  <frame id="8">0X000000000004B6FF</frame>
  <frame id="9">0X0000000000568FB6</frame>
  <frame id="10">0X0000000000569175</frame>
  <frame id="11">0X0000000000569839</frame>
  <frame id="12">0X0000000000569502</frame>
  <frame id="13">0X00000000753337D7</frame>
  <frame id="14">0X0000000075333894</frame>
  <frame id="15">0X00000000775EF56D</frame>
 </Stack>
</Record>
<Record id="1" type="RING_BUFFER_CONNECTIVITY" time="18230162">
 <ConnectivityTraceRecord>
  <RecordType>ConnectionClose</RecordType>
  <RecordSource>Tds</RecordSource>
  <Spid>51</Spid>
  <SniConnectionId>55D5EE76-3898-4D75-94B9-58CC5E90E4D5</SniConnectionId>
  <SniProvider>4</SniProvider>
  <RemoteHost>&lt;local machine&gt;</RemoteHost>
  <RemotePort>0</RemotePort>
  <LocalHost />
  <LocalPort>0</LocalPort>
  <RecordTime>5/17/2011 20:39:34.921</RecordTime>
  <TdsBuffersInformation>
   <TdsInputBufferError>0</TdsInputBufferError>
   <TdsOutputBufferError>0</TdsOutputBufferError>
   <TdsInputBufferBytes>96</TdsInputBufferBytes>
  </TdsBuffersInformation>
  <TdsDisconnectFlags>
   <PhysicalConnectionIsKilled>0</PhysicalConnectionIsKilled>
   <DisconnectDueToReadError>0</DisconnectDueToReadError>
   <NetworkErrorFoundInInputStream>0</NetworkErrorFoundInInputStream>
   <ErrorFoundBeforeLogin>0</ErrorFoundBeforeLogin>
   <SessionIsKilled>0</SessionIsKilled>
   <NormalDisconnect>0</NormalDisconnect>
   <NormalLogout>0</NormalLogout>
  </TdsDisconnectFlags>
 </ConnectivityTraceRecord>
 <Stack>
  <frame id="0">0X00000000013CC34B</frame>
  <frame id="1">0X00000000013C925E</frame>
  <frame id="2">0X000000000068C099</frame>
  <frame id="3">0X000000000004BBD8</frame>
  <frame id="4">0X000000000004B8BA</frame>
  <frame id="5">0X000000000004B6FF</frame>
  <frame id="6">0X0000000000568FB6</frame>
  <frame id="7">0X0000000000569175</frame>
  <frame id="8">0X0000000000569839</frame>
  <frame id="9">0X0000000000569502</frame>
  <frame id="10">0X00000000753337D7</frame>
  <frame id="11">0X0000000075333894</frame>
  <frame id="12">0X00000000775EF56D</frame>
  <frame id="13">0X0000000077723281</frame>
 </Stack>
</Record>

In my case, the first record returns 18456 error which can be further diagnosed here: http://www.eraofdata.com/blog/sql-18456-login-failures/

Share

Share your thoughts