TCP Chimney Setting and SQL Server Error: TCP Provider: An existing connection was forcibly closed by the remote host

You might have heard me discuss the TCP Chimney settings before. If not, don't worry as this post is here to make sure you know about the setting and make sure it is disabled for all your Microsoft Dynamics GP installs.

My earlier post which mentions the TCP Chimney settings, discusses an issue with a large SmartList query not returning all the expected data. This post aims to discuss the bigger picture without referencing a specific module or function.

The Problem

When you login to Microsoft Dynamics GP, a connection to the SQL Server is created which has a SPID (Server Process ID) associated with it. As you work with Dynamics, it uses this connection to continuously communicate to the SQL Server. When Dynamics creates SQL temporary tables, they are created as ##tables and linked to the current SPID. The tables exist until the Dynamics code finishes with the table and closes it, or until the connection for the SPID is broken and SQL "cleans up" and removes the table.

If the connection is lost for any reason, Dynamics will re-establish a new connection with a new SPID so you can continue working. However, any queries currently in process will stop as they were for the old SPID. Also temporary tables created for the now disconnected SPID are not available to the new SPID as they were destroyed by SQL when the old SPID was lost. The fact that Dynamics automatically reconnects could make it appear as though nothing has happened, so the end user might not realise they had been disconnected.

Losing the connection can cause:

• Errors attempting to access Temporary tables that are no longer available, usually generating an DBMS Error 12 (Syntax Error):

The stored procedure createSQLTmpTable returned the following results: DBMS: 12, Microsoft Dynamics GP: 0.

The stored procedure createTmpTable returned the following results: DBMS: 12, Microsoft Dynamics GP: 0.

Note: This issue is most common on windows that use ##temp tables to populate scrolling windows. Two primary examples of windows which behave this way are the RM and PM Inquiry windows (see article in More Information section below).
  

• Datasets returned to be incomplete, such as SmartList queries (see article in More Information section below). This can be seen in the DEXSQL.LOG as

/*  Date: 06/20/2013  Time: 1:43:08
SQLSTATE:(08S01) Native Err:(10054) stmt(21931584):*/
[Microsoft][SQL Server Native Client 10.0]TCP Provider: An existing connection was forcibly closed by the remote host.
*/
 

•  Posting Interruptions, forcing transactions into Batch Recovery

Possible Causes

There can be a number of reasons for the SQL Connection to be lost between the SQL Server and a client workstations, including but not limited to:

• Workstations set to Stand By, Sleep or Hibernate when not used for a period of time.
• Timeout settings on SQL Server, disconnecting idle sessions.
• Faulty network infrastructure causing the connection to the server to be dropped. This includes bad Wireless quality.
• Not installing critical updates, causing a faulty network driver to drop the connection.
• The Database Property under Options "Auto Close" was set to True allowing the server to close "inactive" connections. This must be set to False.

But there are also a couple of settings which are known to cause disconnections. These are the TCP Chimney setting and the SyncAttackProtect setting. Disabling these settings on both the Server and the client has been known to resolve unexplained disconnections.

The Solution

Below are the steps from my previous "SmartList" article with the steps to disable the features and the links to the relevant KB articles: 

The following article explains the issues with the TCP Chimney primarily for a Windows Server 2003 machine:

"General Network error," "Communication link failure," or "A transport-level error" message when an application connects to SQL Server (KB 942861)

The following registry changes will disable the TCP Chimney feature on a Windows Server 2003 machine:

1. Launch regedit.exe
2. Edit DWORD EnableTCPChimney under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters, set data value to 0
3. Edit DWORD EnableRSS under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters, set data value to 0
4. Edit DWORD EnableTCPA under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters, set data value to 0
5. Changes take effect after a reboot. 

The following article explains how to disable the TCP Chimney for a Windows Server 2008 machine.

Information about the TCP Chimney Offload, Receive Side Scaling, and Network Direct Memory Access features in Windows Server 2008 (KB 951037)

The following command line command will disable the TCP Chimney feature on a Windows Server 2008 or later machine:

netsh int tcp set global chimney=disabled

Troubleshooting: Connection Forcibly Closed (TechNet Article)

The following registry changes will disable SynAttackProtect feature:

1. Launch regedit.exe
2. Add DWORD value named SynAttackProtect under registry key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\, set value to 0
3. Changes take effect after a reboot

More Information

For more information on this and related issues, see the following articles:

• Why does SmartList not return all of the expected data for large queries?
   
• "The stored procedure createSQLTmpTable returned the following results: DBMS: 12" exceptions

I hope the helps resolve some of those unexpected and weird errors for you. 

David

• Anonymous
  February 25, 2014
  The related article by Kayla Schilling from The Resource Group is worth a read: dynamicsuniversity.com/.../fp-couldnt-close-table-one-setting-may-end-random-disconnects-your-gp-databases

• Anonymous
  October 07, 2014
  Sir, I tried your solution above but I am still having problems with my sql connection. What is confusing is that I have set up a new server, restored a backup copy of the database and until now, I am NOT  having any problems. But with the old servers, I am having this TCP errors. As I remember, these error started showing when I executed a code that have sql queries inside a loop. At first, the code executed completely but having ran it for several times, the errors started to show and now, even a simple query that returns a hundred or so records causes these tcp errors. Sometimes, this simple query executes but most of the time, an error occurs. Please help, I have these problems for a long time that even our DB admin is clueless how to resolve the problem. Thanks.