TCP Provider, error: 0- An existing connection was forcibly closed by the remote host

Once in a while, one comes across a problem that is so totally perplexing, and people have various success with different approaches that it makes me think of the tale of the blind men and the elephant.

Recently, in a production environment, we are seeing errors in our SQL Server clients: reporting

TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host

Summary: The TCP connection from client to server no longer exists.

Here are a sundry list of possible reasons gleaned from web searches.

Reasons:

1. Most likely reason – TCP Chimney Offload feature was enabled
2. Flaky TCP connections
3. Scalable Networking Pack
4. Flaky Network Cards/Drivers
5. Denial of Service Protection
6. Connection Pooling fetched a dead connection from the pool
7. Makes no sense

Flaky Network Connections

Source: Matt Neerincx (MSFT)


net stop sqlbrowser

cd "C:\Program Files\Microsoft SQL Server\90\Shared"

sqlbrowser -c

This will show you in real time when incoming requests hit the browser.

Source:MSDN forums

==== This may be helpful as well, if there are alot of returned @@PACKET_ERRORS ===

USE tempdb
GO
set nocount on
go

PRINT ':: Relative time spent on I/O, CPU, and idle since server start'
SELECT @@CPU_BUSY AS [@@CPU_BUSY], @@IDLE AS [@@IDLE], @@IO_BUSY AS [@@IO_BUSY],
CONVERT (varchar(8), CONVERT (numeric (6, 4), (100.0 * @@CPU_BUSY / (@@CPU_BUSY + @@IDLE + @@IO_BUSY)))) + '%' AS Pct_CPU_BUSY,
CONVERT (varchar(8), CONVERT (numeric (6, 4), (100.0 * @@IDLE / (@@CPU_BUSY + @@IDLE + @@IO_BUSY)))) + '%' AS Pct_IDLE,
CONVERT (varchar(8), CONVERT (numeric (6, 4), (100.0 * @@IO_BUSY / (@@CPU_BUSY + @@IDLE + @@IO_BUSY)))) + '%' AS Pct_IO_BUSY
PRINT ''
PRINT ':: Misc network and I/O stats'
SELECT @@PACK_RECEIVED AS [@@PACK_RECEIVED], @@PACK_SENT AS [@@PACK_SENT],
@@PACKET_ERRORS AS [@@PACKET_ERRORS (network errors e.g. 17824)]
SELECT @@TOTAL_READ AS [@@TOTAL_READ], @@TOTAL_WRITE AS [@@TOTAL_WRITE],
@@TOTAL_ERRORS AS [@@TOTAL_ERRORS (disk read/write I/O errors)]
PRINT ''
PRINT ':: GETDATE()'
PRINT CONVERT (varchar, GETDATE(), 109)
PRINT ''
PRINT 'Done.'
GO


Scalable Networking Pack

Source: MS KB 942861 (via Raman Gosala)

Cause —This problem occurs because the TCP Chimney Offload feature is enabled on the server. The TCP Chimney Offload feature is enabled by the Windows Server 2003 Scalable Networking Pack.

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

This problem may also occur when the network adapter uses other chipsets

Source: SQL Server and TCP Chimney – Cindy Gross

If you are using SQL Server or Analysis Services: I suggest you double check that your SNP settings, especially TCP Chimney Offset, are all OFF unless your NIC vendor has verified they support it and you have installed their version of drivers that support it. Windows 2003 SP2 turned it on by default, you can disable it with a hotfix (which updates three registry key values) or manually set the registry key values yourself. If the NIC vendor does support the settings they can improve your network performance, but when they don’t support it you can see odd connectivity problems.

Denial of Service Protection

Source: MSDN – Troubleshooting Connection Forcibly Closed

Cause	Resolution
Client is connecting with an unsupported version of the SQL Server Native Client.	Update the client computer to the server version of the SQL Server Native Client.
Faulty network hardware is dropping portions of the TCP traffic.	Use network monitoring programs to analyze TCP SYN, ACK, and FIN messages.
The SynAttackProtect setting may be dropping connections.	See the section “Connections May Be Forcibly Closed When Running on Windows Server 2003 SP1″ that follows.

Occurs on Windows Server 2003 SP1

To resolve this issue, use the regedit.exe utility to add the following registry key:

Key	Type	Name	Value
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\	DWORD	SynAttackProtect	00000000

Connection Pooling

Turn off connection pooling. Note: this has adverse performance issues if you are constantly reconnecting to the server on every request.

Miscellaneous

Source: DBASpot

I went into the Sql Server Configuration Manager, Sql Native Client Configuration -> Protocols and disabled Shared Memory and made the TCP/IP protocol #1 in order. Then just restarted the Sql Service and it all started working!

You should follow me on twitter here