Idle Connection with Keep-Alive Packets

Commented Trace

Frame Time Offset Source IP    Dest IP      Description
----- ----------- ------------ ------------ ---------------------------------------------------------------------------------------------------
--- Command and Data
41145 218.9041090  10.10.10.22  10.10.10.55 TDS:SQLBatch, Version = 7.300000(No version information available, using the default version), SPID
41146 218.9044980  10.10.10.55  10.10.10.22 TDS:Response, Version = 7.300000(No version information available, using the default version), SPID
41149 218.9057570  10.10.10.55  10.10.10.22 TCP:Flags=...A...., SrcPort=3433, DstPort=62337, PayloadLen=0, Seq=3891874348, Ack=485443881, Win=8

--- Connection is idle for 30 seconds. Keep-Alive packets check the viability of the connection.
57282 248.9036660  10.10.10.22  10.10.10.55 TCP:[Keep alive]Flags=...A...., SrcPort=62337, DstPort=3433, PayloadLen=1, Seq=485443880 - 48544388
57285 248.9037680  10.10.10.55  10.10.10.22 TCP:[Keep alive ack]Flags=...A...., SrcPort=3433, DstPort=62337, PayloadLen=0, Seq=3891874348, Ack=
57291 248.9188970  10.10.10.55  10.10.10.22 TCP:[Keep alive]Flags=...A...., SrcPort=3433, DstPort=62337, PayloadLen=1, Seq=3891874347 - 3891874
57293 248.9189240  10.10.10.22  10.10.10.55 TCP:[Keep alive ack]Flags=...A...., SrcPort=62337, DstPort=3433, PayloadLen=0, Seq=485443881, Ack=3

--- Another Keep-Alive exchange after 30 more seconds
42618 278.9030510  10.10.10.22  10.10.10.55 TCP:[Keep alive]Flags=...A...., SrcPort=62337, DstPort=3433, PayloadLen=1, Seq=485443880 - 48544388
42620 278.9032150  10.10.10.55  10.10.10.22 TCP:[Keep alive ack]Flags=...A...., SrcPort=3433, DstPort=62337, PayloadLen=0, Seq=3891874348, Ack=
42637 278.9203250  10.10.10.55  10.10.10.22 TCP:[Keep alive]Flags=...A...., SrcPort=3433, DstPort=62337, PayloadLen=1, Seq=3891874347 - 3891874
42639 278.9203730  10.10.10.22  10.10.10.55 TCP:[Keep alive ack]Flags=...A...., SrcPort=62337, DstPort=3433, PayloadLen=0, Seq=485443881, Ack=3

--- Another Keep-alive exchange
03254 308.9042680  10.10.10.22  10.10.10.55 TCP:[Keep alive]Flags=...A...., SrcPort=62337, DstPort=3433, PayloadLen=1, Seq=485443880 - 48544388
03257 308.9044970  10.10.10.55  10.10.10.22 TCP:[Keep alive ack]Flags=...A...., SrcPort=3433, DstPort=62337, PayloadLen=0, Seq=3891874348, Ack=
03263 308.9216870  10.10.10.55  10.10.10.22 TCP:[Keep alive]Flags=...A...., SrcPort=3433, DstPort=62337, PayloadLen=1, Seq=3891874347 - 3891874
03264 308.9217130  10.10.10.22  10.10.10.55 TCP:[Keep alive ack]Flags=...A...., SrcPort=62337, DstPort=3433, PayloadLen=0, Seq=485443881, Ack=3

--- Another Keep-Alive exchange
61960 338.9002510  10.10.10.22  10.10.10.55 TCP:[Keep alive]Flags=...A...., SrcPort=62337, DstPort=3433, PayloadLen=1, Seq=485443880 - 48544388
61969 338.9004440  10.10.10.55  10.10.10.22 TCP:[Keep alive ack]Flags=...A...., SrcPort=3433, DstPort=62337, PayloadLen=0, Seq=3891874348, Ack=
61986 338.9249550  10.10.10.55  10.10.10.22 TCP:[Keep alive]Flags=...A...., SrcPort=3433, DstPort=62337, PayloadLen=1, Seq=3891874347 - 3891874
61987 338.9249850  10.10.10.22  10.10.10.55 TCP:[Keep alive ack]Flags=...A...., SrcPort=62337, DstPort=3433, PayloadLen=0, Seq=485443881, Ack=3

The keep-alive exchange consists of a 4-packet sequence. The exact sequence may vary a little depending on timings. For regular TCP connections, the Keep-Alive exchange happens after the connection has been idle for two hours. For SQL Server connections, it occurs after 30 seconds.

1. After 30 seconds of idle time, machine A (either the client or the server) will send the first Keep-Alive packet. This is like an ACK packet but with a payload of 1 byte.
2. Machine B responds with a regular ACK packet (payload is 0 bytes).
3. Machine B sends its Keep-Alive packet.
4. Machine A responds with an ACK packet.

The Keep-Alive exchange is controlled by the TCP.SYS service in the Windows Kernel and is below the awareness of applications.

This exchange must happen within 1 second.

A connection can go idle for several reasons:

1. The client has turned its focus elsewhere and is done with SQL commands for the time being. The connection could be explicitly left open, which is not a good idea, or it was closed and the connection replaced in the connection pool.
2. If the last packet before the Keep-Alive exchange was a TDS:SQLBatch or a TDS:RPCRequest, then the server could be executing a long-running query.

For short traces, you may see just the Keep-Alive exchanges and no preceding or following commands being executed. This is normal, especially when using connection pooling. Connections may remain idle in the pool for several minutes.

The default command timeout will terminate a command after 20 seconds, but the application may increase the timeout to 120 or even 600 seconds for long-running queries. Setting the Command Timeout to 0 (infinite) is not a best practice as the application might hang indefinitely.