GenerateKillSleepingSessionCommands.sql

/*
Purpose:
- Generate KILL commands for sleeping sessions that meet a conservative idle threshold.

Safety:
- Generates commands only; it never executes KILL.
- Excludes sessions with open transactions, but every result still requires human review.

Requirements:
- SQL Server 2012 or later.
- VIEW SERVER STATE to inspect sessions; ALTER ANY CONNECTION to execute a generated command.

Customization:
- Adjust @MinimumIdleMinutes and add login, host, program, or database filters.
*/

SET NOCOUNT ON;

DECLARE @MinimumIdleMinutes INT = 120;

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    DB_NAME(s.database_id) AS database_name,
    DATEDIFF(MINUTE, s.last_request_end_time, SYSDATETIME()) AS idle_minutes,
    N'KILL ' + CONVERT(NVARCHAR(12), s.session_id) + N';' AS command_to_review
FROM sys.dm_exec_sessions AS s
WHERE s.is_user_process = 1
  AND s.status = N'sleeping'
  AND s.session_id <> @@SPID
  AND s.open_transaction_count = 0
  AND s.last_request_end_time <= DATEADD(MINUTE, -@MinimumIdleMinutes, SYSDATETIME())
ORDER BY idle_minutes DESC, s.session_id;