Skip to content

Connection Dropped in both Directions

Jump to bottom
Malcolm Stewart edited this page Jul 2, 2021 · 2 revisions

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.

Clone this wiki locally