tracing tricky connect issues in SQLServer 2008

 

SQL Server 2008 contains a new feature designed to help troubleshoot particularly tricky connectivity problems. This new feature, the Connectivity Ring Buffer, captures a record for each server-initiated connection closure, including each kill of a session or Login Failed event. To facilitate efficient troubleshooting, the ring buffer attempts to provide information to correlate between the client failure and the server’s closing action. Up to 1k records in the ring buffer are persisted for as long as the server is online, and after 1000 records, the buffer wraps around and begins replacing the oldest records. The Connectivity Ring Buffer’s records are exposed by a DMV which can be queried:

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

from http://blogs.msdn.com/sql_protocols/archive/2008/05/20/connectivity-troubleshooting-in-sql-server-2008-with-the-connectivity-ring-buffer.aspx

But…  I’m on 2005 and am getting a lot of

“SQL Native Client TCP Provider An existing connection was forcibly closed by the remote host” in my APP server and BAT server jde logs.

I think that it might be network related, but I’ll keep you posted.

Right, so we’ve come a long way with this error.

It seems that this is related to either:

EnableTCPChimney = 0  Follow http://support.microsoft.com/kb/945977 to disable

Another registry change DisableTaskOffload=1  http://blogs.technet.com/brad_rutkowski/archive/2007/08/10/how-to-know-if-tcp-offload-is-working.aspx

There is a lot of documentation on some bad NICs http://support.microsoft.com/kb/942861

Typically, this problem occurs when the network adapter uses the Broadcom 5708 chipset. For example, the following network adapters use the Broadcom 5708 chipset:

• Broadcom NetXtreme II
• Hewlett-Packard NC373i Multifunction Gigabit Server Adapter

So, disable Chimney, disableTaskOffload, get the latest NIC drivers & bios