How does SQL Server clear up orphaned connections? - 01 Jun 1999

A. It doesn't. It never terminates a connection unless it is told to by a user, a KILL command is issued, or the operating system tells it that the network connection it is using has been disconnected.

How long the operating system takes to kill a network connection, or whether it is done at all, depends on the net-lib and network protocol used. For parameters on keep-alive frames and session time-outs for the relevant network protocol the best guide is the NT Server resource kit, which describes how NT's various network layers work. See Q137983
for some details pertinent to SQL Server.

Typically, named-pipe connections over netbeui will be timed out quite quickly, followed by named-pipes over IP. If you're using tcp-ip sockets then these sessions aren't timed-out at all by default.