How to Kill Processes Idle for X Hours? – Interview Question of the Week #152

Question: How can I find SQL Server sessions idle for several hours, and should I kill them?

Answer: My first reaction when I heard this interview question was, “Why would you need to?” The real case involved a .NET application that opened connections and failed to close them. The lasting fix belongs in the application. While the developers investigate, a DBA needs a way to identify the sessions causing trouble without assuming every quiet connection is abandoned.

A common script loops over sys.sysprocesses with a cursor and runs KILL for every session with an old last_batch. I would not automate that from an age threshold. A sleeping session can have an open transaction; killing it rolls that work back and can take time. An apparently old connection may also be intentionally pooled. First, collect evidence.

DECLARE @IdleHours int = 8;
SELECT s.session_id,
       s.login_name,
       s.host_name,
       s.program_name,
       s.status,
       s.last_request_end_time,
       s.open_transaction_count,
       DATEDIFF(minute, s.last_request_end_time, SYSDATETIME())
           AS idle_minutes
FROM sys.dm_exec_sessions AS s
WHERE s.is_user_process = 1
  AND s.session_id <> @@SPID
  AND s.status = N'sleeping'
  AND s.last_request_end_time <
      DATEADD(hour, -@IdleHours, SYSDATETIME())
  AND NOT EXISTS
      (SELECT 1 FROM sys.dm_exec_requests AS r
       WHERE r.session_id = s.session_id)
ORDER BY s.last_request_end_time;

The query only previews candidates. last_request_end_time is the end of the last request, not proof that the client is gone. The open_transaction_count column is essential context: an idle session can still hold an open transaction and its locks. You need permission to see other sessions.

Suppose a specific connection is confirmed as the leak and must go. Coordinate with the application owner, recheck the session details and transaction state, and issue KILL only for that reviewed session ID. Session IDs can be reused, so don’t act later from an old exported list. Check rollback status if termination has to unwind work. For a recurring incident, repair the connection handling and monitor the pool rather than scheduling a cursor to kill everything that looks idle.

That’s the interview lesson: “idle for eight hours” is a useful filter for investigation, not a safety test for termination. If you have a safer diagnostic for a particular connection leak, I would be interested to see it in the comments.

An idle session is not an abandoned one, it is a connection that may still hold a transaction, so look before you KILL.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.