Connection Dropped in both Directions - microsoft/CSS_SQL_Networking_Tools GitHub Wiki
Connection Dropped in both Directions
This topic shows an example of a network device that drops packets from the server to the client and also from the client to the server.
Client Trace Ending
Frame Time Offset Source IP Dest IP Description
----- ----------- ----------- ----------- ---------------------------------------------------------------------------------------------------
--- Response packets from SQL Server
60061 55.3953720 10.10.10.20 10.10.10.10 TDS:Response, Version = 7.4 (0x74000004), SPID = 57, PacketID = 1, Flags=...A...., SrcPort=1433, Ds
60062 55.3954201 10.10.10.20 10.10.10.10 TCP:[Continuation to #60061]Flags=...A...., SrcPort=1433, DstPort=59379, PayloadLen=1310, Seq=15194
60063 55.3954497 10.10.10.20 10.10.10.10 TCP:[Continuation to #60061]Flags=...A...., SrcPort=1433, DstPort=59379, PayloadLen=1310, Seq=15194
60064 55.3954668 10.10.10.20 10.10.10.10 TCP:[Continuation to #60061]Flags=...AP..., SrcPort=1433, DstPort=59379, PayloadLen=166, Seq=151947
60065 55.3954918 10.10.10.20 10.10.10.10 TDS:Continuous Response, Version = 7.4 (0x74000004), SPID = 57, PacketID = 2, Flags=...AP..., SrcPo
--- Client Acknowledgement
60067 55.3956833 10.10.10.10 10.10.10.20 TCP:Flags=...A...., SrcPort=59379, DstPort=1433, PayloadLen=0, Seq=4284756372, Ack=1519475592, Win=
--- New Query
60077 55.4246119 10.10.10.10 10.10.10.20 TDS:SQLBatch, Version = 7.4 (0x74000004), SPID = 0, PacketID = 1, Flags=...AP..., SrcPort=59379, Ds
--- No ACK from the server so we retransmit
60596 55.6771809 10.10.10.10 10.10.10.20 TCP:[ReTransmit #60077]Flags=...AP..., SrcPort=59379, DstPort=1433, PayloadLen=178, Seq=4284756372
60884 55.9773598 10.10.10.10 10.10.10.20 TCP:[ReTransmit #60077]Flags=...AP..., SrcPort=59379, DstPort=1433, PayloadLen=178, Seq=4284756372
61875 56.5777374 10.10.10.10 10.10.10.20 TCP:[ReTransmit #60077]Flags=...AP..., SrcPort=59379, DstPort=1433, PayloadLen=178, Seq=4284756372
63077 57.7793939 10.10.10.10 10.10.10.20 TCP:[ReTransmit #60077]Flags=...AP..., SrcPort=59379, DstPort=1433, PayloadLen=178, Seq=4284756372
64736 60.1794183 10.10.10.10 10.10.10.20 TCP:[ReTransmit #60077]Flags=...AP..., SrcPort=59379, DstPort=1433, PayloadLen=178, Seq=4284756372
68156 64.9791042 10.10.10.10 10.10.10.20 TCP:[ReTransmit #60077]Flags=...AP..., SrcPort=59379, DstPort=1433, PayloadLen=178, Seq=4284756372
--- And reset the connection after a few tries (19 seconds of waiting [offset 74 - 55])
75602 74.5786797 10.10.10.10 10.10.10.20 TCP:Flags=...A.R.., SrcPort=59379, DstPort=1433, PayloadLen=0, Seq=4284756550, Ack=1519475592, Win=
Server Trace Ending
Frame Time Offset Source IP Dest IP Description
----- ----------- ----------- ----------- ---------------------------------------------------------------------------------------------------
--- Same query response packets as seen in the client trace
4215 70.8358708 10.10.10.20 10.10.10.10 TDS:Response, Version = 7.4 (0x74000004), SPID = 57, PacketID = 1, Flags=...A...., SrcPort=1433, Ds
4216 70.8358708 10.10.10.20 10.10.10.10 TCP:[Continuation to #4215]Flags=...A...., SrcPort=1433, DstPort=59379, PayloadLen=1310, Seq=151947
4217 70.8358708 10.10.10.20 10.10.10.10 TCP:[Continuation to #4215]Flags=...A...., SrcPort=1433, DstPort=59379, PayloadLen=1310, Seq=151947
4218 70.8358708 10.10.10.20 10.10.10.10 TCP:[Continuation to #4215]Flags=...AP..., SrcPort=1433, DstPort=59379, PayloadLen=166, Seq=1519474
4219 70.8371153 10.10.10.20 10.10.10.10 TDS:Continuous Response, Version = 7.4 (0x74000004), SPID = 57, PacketID = 2, Flags=...AP..., SrcPo
--- Client acknowledges the data
4220 70.8697925 10.10.10.10 10.10.10.20 TCP:Flags=...A...., SrcPort=59379, DstPort=1433, PayloadLen=0, Seq=4284756372, Ack=1519475592, Win=
--- SQL Server does not receive the next query, after 30 seconds, we send a Keep-Alive packet
5692 100.8880666 10.10.10.20 10.10.10.10 TCP:[Keep alive]Flags=...A...., SrcPort=1433, DstPort=59379, PayloadLen=1, Seq=1519475591 - 1519475
5723 101.9092006 10.10.10.20 10.10.10.10 TCP:[Keep alive]Flags=...A...., SrcPort=1433, DstPort=59379, PayloadLen=1, Seq=1519475591 - 1519475
5768 102.9248004 10.10.10.20 10.10.10.10 TCP:[Keep alive]Flags=...A...., SrcPort=1433, DstPort=59379, PayloadLen=1, Seq=1519475591 - 1519475
5819 103.9247790 10.10.10.20 10.10.10.10 TCP:[Keep alive]Flags=...A...., SrcPort=1433, DstPort=59379, PayloadLen=1, Seq=1519475591 - 1519475
5897 104.9247670 10.10.10.20 10.10.10.10 TCP:[Keep alive]Flags=...A...., SrcPort=1433, DstPort=59379, PayloadLen=1, Seq=1519475591 - 1519475
5950 105.9248374 10.10.10.20 10.10.10.10 TCP:[Keep alive]Flags=...A...., SrcPort=1433, DstPort=59379, PayloadLen=1, Seq=1519475591 - 1519475
5982 106.9248071 10.10.10.20 10.10.10.10 TCP:[Keep alive]Flags=...A...., SrcPort=1433, DstPort=59379, PayloadLen=1, Seq=1519475591 - 1519475
6012 107.9289738 10.10.10.20 10.10.10.10 TCP:[Keep alive]Flags=...A...., SrcPort=1433, DstPort=59379, PayloadLen=1, Seq=1519475591 - 1519475
6088 108.9290317 10.10.10.20 10.10.10.10 TCP:[Keep alive]Flags=...A...., SrcPort=1433, DstPort=59379, PayloadLen=1, Seq=1519475591 - 1519475
6137 109.9289457 10.10.10.20 10.10.10.10 TCP:[Keep alive]Flags=...A...., SrcPort=1433, DstPort=59379, PayloadLen=1, Seq=1519475591 - 1519475
--- After 10 Keep-Alive attempts, SQL Server [actually TCP.SYS] resets the connection
6157 110.9289921 10.10.10.20 10.10.10.10 TCP:Flags=...A.R.., SrcPort=1433, DstPort=59379, PayloadLen=0, Seq=1519475592, Ack=4284756372, Win=
Explanation
In the case where a connection is silently dropped instead of actively reset by a device, both the client and server have to independently figure out the issue. In the client case, the connection was dropped right before it sent a new query. SQL does not get the packet so it cannot respond. With a data packet, generally the packet will be retransmitted about 5 times, dependent on specific network settings.
In the server trace, the server does not receive anything, so it assumes the connection has gone idle and sends a Keep-Alive packet after 30 seconds. The packet does not get to the client, so there is no acknowledgement. Because of the non-acknowledgement, it retransmits the Keep-Live packet once a second, and after 10 Keep-Alive packets, resets the connection a second later.
The query packets from the client do not reach the server. The Keep-Alive packets from the server do not reach the client. This means the connection was dropped in both directions.
Action at this point would be to check the logs of routers or other smart devices on the network and see which device may have gotten overloaded and just dropped the conversation altogether. In a VM situation, tracing the VM host can also let you know if the packets were dropped on the virtual network or on the physical network. If dropped on the virtual network, the VM host may have to be tuned to allow for more throughput.