Это хороший вопрос. Ответ будет состоять из двух частей. Первая - общеизвестна: при убиении соединения SQL Server освобождает все наложенные им блокировки. Это легко проиллюстрировать. Откройте два коннекта. Сделайте на первом

begin tran
select * from orders (tablockx holdlock)

на втором

sp_lock:
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51 6 0 0 DB S GRANT
51 6 21575115 0 TAB X GRANT
53 1 85575343 0 TAB IS GRANT

и еще раз

kill 51
sp_lock:
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
53 1 85575343 0 TAB IS GRANT

Вторая часть менее тривиальна и состоит в том, как SQL Server понимает, что соединение сдохло. За это отвечают его сетевые библиотеки. Раньше все было просто: если таймаут кончился, а Ack не получен, клиент отключался нафиг за неуплату. Для TCP/IP время ожидания сидит, напр., в KeepAlive в HKLM\System\CurrentControlSet\Services\TcpIp\Parameters. Аналогично для ост.протоколов. После того, как в Windows 2000 появилась поддержка пресловутой IntelliSense, жизнь, с одной стороны, стала легче (напр., она мгновенно распознает выдергивание кабеля), с другой - процедура настройки какая-то лично для меня неочевидная. Вроде, кажется, все уже выставил - он все равно может случайным образом продержать его все выходные.