Oct 10, 2023 10:16:45 AM
Jun 20, 2022 4:38:05 AM
Inactive TCP/IP connections to SQL Server can sometimes be closed by your router or firewall. This will give your task a recoverable environmental error. Parameters in the Windows Registry can be set to send transmissions for the connection to keep it active.
00026464: 2021-10-27T05:08:24 [SOURCE_CAPTURE ]E: RetCode: SQL_ERROR SqlState: 08S01 NativeError: 10054 Message: [Microsoft][SQL Server Native Client 11.0]TCP Provider: An existing connection was forcibly closed by the remote host. [1022502] (ar_odbc_stmt.c:3977)
Environment
Qlik Replicate
SQL Server as a source
Windows environment
Resolution
Registry parameters can be added on the Replicate Server with Registry Editor.
Add the following dword keys and the specified decimal values:
- HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\TcpWindowSize=256000
- HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\GlobalMaxTcpWindowSize=16777216
- HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\KeepAliveInterval=1000
- HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\KeepAliveTime=60000
Follow these steps to modify the registry manually:
- Open Registry Editor and navigate to the correct location:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters - Right-click Parameters to bring up the option to add dword keys
- Add the following parameters:
- KeepAliveTime
- KeepAliveInterval
- GlobalMaxTcpWindowSize
- TcpWindowSize
- One by one, right-click each parameter and select Modify to modify their DWORD values:
- KeepAliveTime: 60000
- KeepAliveInterval: 1000
- GlobalMaxTcpWindowSize: 16777216
- TcpWindowSize: 256000
- Restart the machine.
For more information on the Windows registry parameters, please refer to Microsoft documentation:
Description of Windows TCP features
KeepAliveTime
