Sql server ошибка 4014

Some times you may observe 4014 error messages in SQL Server errorlog, like below
Error: 4014, Severity:20, State: 11.
A fatal error occurred while reading the input stream from the network. The session will be terminated

To continue analyze it, we should firstly know which is the issue client servers, and we can schedule to run below script during the issue most frequent time:

;WITH RingBufferConnectivity as
( SELECT
records.record.value(‘(/Record/@id)[1]’, ‘int’) AS [RecordID],
records.record.value(‘(/Record/ConnectivityTraceRecord/RecordType)[1]’, ‘varchar(max)’) AS [RecordType],
records.record.value(‘(/Record/ConnectivityTraceRecord/RecordTime)[1]’, ‘datetime’) AS [RecordTime],
records.record.value(‘(/Record/ConnectivityTraceRecord/SniConsumerError)[1]’, ‘int’) AS [Error],
records.record.value(‘(/Record/ConnectivityTraceRecord/State)[1]’, ‘int’) AS [State],
records.record.value(‘(/Record/ConnectivityTraceRecord/Spid)[1]’, ‘int’) AS [Spid],
records.record.value(‘(/Record/ConnectivityTraceRecord/RemoteHost)[1]’, ‘varchar(max)’) AS [RemoteHost],
records.record.value(‘(/Record/ConnectivityTraceRecord/RemotePort)[1]’, ‘varchar(max)’) AS [RemotePort],
records.record.value(‘(/Record/ConnectivityTraceRecord/LocalHost)[1]’, ‘varchar(max)’) AS [LocalHost]
FROM
( SELECT CAST(record as xml) AS record_data
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type= ‘RING_BUFFER_CONNECTIVITY’
) TabA
CROSS APPLY record_data.nodes(‘//Record’) AS records (record)
)
SELECT RBC.*, m.text
FROM RingBufferConnectivity RBC
LEFT JOIN sys.messages M ON
RBC.Error = M.message_id AND M.language_id = 1033
WHERE RBC.RecordType=’Error’ and RBC.Error=4014
ORDER BY RBC.RecordTime DESC

Check the column of ‘RemoteHost’ and find one of the most frequent IP, and you will need to collect network monitor between the server and SQL Server.

So generally speaking, it was when the network tried to sperate a large packets, it lost one of them, it can be caused by TCPChimney offloading or other network communication issues, you need engage your network team to continue check where was the packet lost.

One of our servers would periodically suffer from a fatal error during batch processing. This was a SQL Server 2008 instance running on Windows 2008 R2.

The error in question:

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.

Solution

The problem is related to Microsoft’s Scalable Networking Pack (SNP) which consists of these three main features:

• TCP/IP Chimney Offload – helps to transfer the workload away from the CPU to a network adapter during network data transfer. This helps to reduce the processing load on the CPU.
• Receive Side Scaling (RSS)- enabled the network load from a NIC 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.

So first I checked the settings on the server by running the following command:

netsh int tcp show global

This returns:

As we can see these settings were all enabled. Windows Server 2008 disables TCP Chimney Offload by default so it seems an application has enabled this feature.

If we drill into the NIC driver settings we find:

So we can see the NIC vendor has enabled this feature in the driver so these need to be disabled as highlighted above.

Now we need to disable these settings at the OS level by running these commands in the command prompt:

Disable TCP/IP Chimney Offload:

netsh int tcp set global chimney=disabled

Disable RSS:

netsh int tcp set global rss=disabled

1 ) Disabling NetDMA can only be achieved by modifying the registry so make sure you take a backup first.

2) Locate the registry sub-key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

3) 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.

4) 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.

Note NetDMA requires a system reboot to take effect. To confirm the changes have been made run the netsh int tcp show global command again.