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.
See below analysis sample of network trace:
Client sent 8000 bytes data:
123.45.0.789 789.123.0.45 TDS TDS:Continuous BulkLoadBCP, Version = Undefined TDS version(0x74000004), SPID = 0, PacketID = 4, Flags=...AP..., SrcPort=54541, DstPort=52343, PayloadLen=8000, Seq=1077286218 - 1077294218, Ack=3825055476, Win=262656
The packets was large so it was divided, but server only received 1460*5=7300 bytes of data, so it lost 700 bytes of data
123.45.0.789 789.123.0.45 TDS TDS:Continuous BulkLoadBCP, Version = Undefined TDS version(0x74000004), SPID = 0, PacketID = 4, Flags=...A...., SrcPort=54541, DstPort=52343, PayloadLen=1460, Seq=1077286218 - 1077287678, Ack=3825055476, Win=262656
123.45.0.789 789.123.0.45 TCP TCP:[Continuation to #96]Flags=...A...., SrcPort=54541, DstPort=52343, PayloadLen=1460, Seq=1077287678 - 1077289138, Ack=3825055476, Win=1026 (scale factor 0x8) = 262656
123.45.0.789 789.123.0.45 TCP TCP:[Continuation to #96]Flags=...A...., SrcPort=54541, DstPort=52343, PayloadLen=1460, Seq=1077289138 - 1077290598, Ack=3825055476, Win=1026 (scale factor 0x8) = 262656
123.45.0.789 789.123.0.45 TCP TCP:[Continuation to #96]Flags=...A...., SrcPort=54541, DstPort=52343, PayloadLen=1460, Seq=1077290598 - 1077292058, Ack=3825055476, Win=1026 (scale factor 0x8) = 262656
123.45.0.789 789.123.0.45 TCP TCP:[Continuation to #96]Flags=...A...., SrcPort=54541, DstPort=52343, PayloadLen=1460, Seq=1077292058 - 1077293518, Ack=3825055476, Win=1026 (scale factor 0x8) = 262656
Then the server reset the communication:
123.45.0.789 789.123.0.45 TCP TCP:Flags=.....R.., SrcPort=52343, DstPort=54541, PayloadLen=0, Seq=3825055476, Ack=1077293518, Win=0
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.
