FindSleepingSessions.sql

/*
Purpose:
- List sleeping user sessions whose last request ended before a chosen threshold.

Safety:
- Read-only. A sleeping session can still own an open transaction; inspect open_transaction_count.

Requirements:
- SQL Server 2012 or later.
- VIEW SERVER STATE, or the equivalent server performance-state permission on newer versions.

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

SET NOCOUNT ON;

DECLARE @MinimumIdleMinutes INT = 60;

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    DB_NAME(s.database_id) AS database_name,
    s.status,
    s.open_transaction_count,
    s.last_request_start_time,
    s.last_request_end_time,
    DATEDIFF(MINUTE, s.last_request_end_time, SYSDATETIME()) AS idle_minutes,
    c.client_net_address,
    c.connect_time,
    c.net_transport,
    c.encrypt_option
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_connections AS c
    ON c.session_id = s.session_id
WHERE s.is_user_process = 1
  AND s.status = N'sleeping'
  AND s.session_id <> @@SPID
  AND s.last_request_end_time <= DATEADD(MINUTE, -@MinimumIdleMinutes, SYSDATETIME())
ORDER BY idle_minutes DESC, s.session_id;