Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Fixing SQL Server fatal error 4014


By:   |   Read Comments (7)   |   Related Tips: More > Error Logs

Attend these FREE MSSQLTips webcasts >> click to register


Problem

One of our SQL Servers was experiencing fatal errors on a frequent basis during batch processing. This was a SQL Server 2008 on Windows 2008 operating system. Here are some details of the error log which were captured:

A fatal error occurred while reading the input stream from the network. The session will be terminated (input error: 10054, output error: 0). Error: 4014, Severity: 20, State: 11.

Fatal error shot captured in error log file
Solution

This fatal error suggests that something is wrong on the network which is causing network packets to drop. The error which is captured in the SQL Server error log can be due to different reasons. I searched this error on the web and found some MSDN forums where similar issues were discussed. Some responses suggested disabling the TCP/IP Chimney Offload feature, so I decided to research this further. Let's start with TCP/IP Chimney offloading, RSS features and NetDMA.

Microsoft released the Scalable Networking Pack (SNP) that consists of three main features. These three features are TCP/IP Chimney, Receive Side Scaling (RSS) and NetDMA.

  • TCP/IP Chimney Offload, as per TechNet, is designed to take processing of the network such as packet segmentation and reassembly processing tasks, from a computer's CPU to a network adapter that supports TCP Chimney Offload. This has the effect of reducing the workload on the host CPU and moving it to the NIC, allowing both the Host OS to perform quicker and also speed up the processing of network traffic.
  • Receive Side Scaling (RSS) enables the network load from a network adapter to be distributed across multiple CPUs in a multiprocessor computer.
  • Network Direct Memory Access (NetDMA) provides services for offloading the memory copy operation that is performed by the networking subsystem to a dedicated direct memory access (DMA) engine when receiving network packets.

Now let's check these settings on our impacted system. We ran the below command to check the existing values of these SNP settings:

netsh int tcp show global

Checked TCP chimney offload

As we can see, all these settings were enabled as shown in the above screenshot. By default, TCP Chimney Offload is disabled in Windows 2008 and later versions, but sometimes vendor applications can turn them on. We can also check whether TCP Chimney Offload is working or not by running the netstat -t command.

I checked these settings on other production servers for the same application and found that these settings were disabled on those servers. See the below output of SNP settings on two production servers:

SERVER: DELHI2013SQL01

TCP Global Parameters
----------------------------------------------
Receive-Side Scaling State          : disabled
Chimney Offload State               : disabled
NetDMA State                        : disabled
Direct Cache Access (DCA)            : disabled
Receive Window Auto-Tuning Level    : normal
Add-On Congestion Control Provider  : ctcp
ECN Capability                      : disabled
RFC 1323 Timestamps                 : disabled


SERVER: DELHI2013SQL02

TCP Global Parameters
----------------------------------------------
Receive-Side Scaling State          : disabled
Chimney Offload State               : disabled
NetDMA State                        : disabled
Direct Cache Access (DCA)            : disabled
Receive Window Auto-Tuning Level    : normal
Add-On Congestion Control Provider  : ctcp
ECN Capability                      : disabled
RFC 1323 Timestamps                 : disabled

The problem is that many NICs report to the OS that they support these features, which they indeed do, but many didn't perform these functions very well in reality. These SNP options looks good from an OS perspective, but due to misbehaving NIC drivers they turn into lot of weird issues so it is better to turn them off to fix such issues, get an update from the NIC vendor or use a better NIC driver that can use these features in an effective way.

We should disable these SNP features in Windows and NIC hardware setting as well with the vendor's firmware tools if we donít have an update for  the NIC driver or NIC drivers are not compatible with these features. We can check these settings in network adaptors by launching device manager and then expanding the network adaptors section as shown in the below screenshot.

SNP settings in netowork adaptors

Steps to Disable NIC Settings

So we decided to replicate the same settings (which were on other production servers) into our impacted server where the fatal errors were reported. We used this Microsoft article to disable the TCP/ IP Chimney, RSS and NetDMA state by using the below steps.

1: Open a command prompt with administrative privileges.

2: Run the below command to disable TCP/IP Chimney Offload.

netsh int tcp set global chimney=disabled

3: Run the below command to disable RSS.

netsh int tcp set global rss=disabled

4: NetDMA will be disabled through the registry, so make sure to backup your registry before doing the next steps. To open the Registry Editor, click Start, click Run, type regedit, and then click OK.

5: Locate the registry sub-key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters and click on it.

6: Locate the EnableTCPA registry entry. If this registry entry does not exist, right-click the Parameters sub-key, point to New, and then click DWORD Value.

7: Replace the New Value #1 by typing EnableTCPA, and then press ENTER. Double-click the EnableTCPA registry value you just created and type 0 in the Value to disable NetDMA, and then click OK.

8: TCP Chimney Offload and RSS will be disabled just after executing the above commands, but NetDMA requires a system reboot after making changes to the registry.

After the above changes and the reboot we can run the below command to check whether all these settings are disabled, which they are as shown below.

netsh int tcp show global

SNP settings disabled on windows

After we made these changes we no longer received any fatal errors. These features are made to enhance the capability of Windows, but unfortunately it is not always useful if your NIC drivers do not support these features.

Next Steps
  • If you run into these errors, use the steps in this tip to see if these features are enabled for your NICs.
  • If you have advanced NIC drivers that can utilize these features then enable TCP Chimney Offloading otherwise it is suggested to turn them off.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, September 12, 2017 - 10:52:27 AM - wedisley Back To Top

Hello Mr. Manvendra,

I have this error - Sql server 2016

a fatal error occurred while reading the input stream from network; The session will be terminated (input error: 0, output error: 0)
error:4014, severity: 20, state:4

 

Doy you have some idea to solve it?

 



Friday, September 25, 2015 - 11:37:12 AM - Alex Back To Top

I have the same issue and the the recommended fix did not correct the problem.

Any other ideas, Manvendra?

 


Monday, March 09, 2015 - 9:41:47 AM - Manvendra Back To Top

Thx Guys...Really appreciate your feedbacks.


Saturday, March 07, 2015 - 9:48:13 AM - Sammy Back To Top

You always touch a new deep area of sql server. Thx for the tip Sir. Very helpful. Request u to write some magical tips on performance tuning as u have written before.


Friday, March 06, 2015 - 8:47:46 AM - Channdeep Singh Back To Top

Thanks a lot sir. Your tips are always very helpful and easy to understand and follow.


Thursday, March 05, 2015 - 8:23:05 AM - Junior Galv„o - MVP Back To Top

Hi Manvedra,

Great article, congratulations.

Good job!

 


Thursday, March 05, 2015 - 2:46:39 AM - Gourang Back To Top

This tip is really helpful!!Thanks Man


Learn more about SQL Server tools