How does SQL Server clear up orphaned connections?

t doesn’t. SQL Server never terminates a connection unless explicitly told to do so (i.e., by a user, by a KILL command, or by the operating system informing it that the network connection has been disconnected).

The length of time it takes for the operating system to kill a network connection can vary greatly. It largely depends on the net-lib and network protocol used.

Typically, named pipe connections over NetBEUI will time out quickly. Named pipes over IP will time out almost as quickly as well. If you’re using TCP/IP sockets, those sessions don’t time out at all by default.

More information on troubleshooting orphaned connections in SQL Server is available here.