Nobody is running a query in the blocking session, yet other requests cannot proceed. Sleeping sessions can retain locks when an application leaves a transaction open. Inspect transaction ownership and the last submitted batch before deciding how to release the work.
Separate Idle Connections From Idle Transactions
Sleeping means the session has no active request at that moment. Connection pooling normally leaves idle connections on the server, so sleeping alone is not an error. The important combination is an idle session, an open transaction, and locks that another request needs. Use that evidence rather than treating every quiet connection as a candidate for termination.
I check transaction count before asking why a connection is still present. A pooled connection without unfinished work can be perfectly healthy. Which application owns the transaction that remains open? Save its login, host, program, and request times. Client supplied host and program labels help investigation, but they are not authenticated proof of the process's identity.
List Sleeping Sessions With Open Transactions
Run this with a monitoring login. Without VIEW SERVER STATE (VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later), a login sees only its own session. The query lists user sessions that are sleeping with open transactions. It returns evidence, not commands to kill them. Idle time indicates elapsed time since the last request ended.
SELECT session_id,login_name,host_name,program_name,status,
open_transaction_count,login_time,last_request_start_time,last_request_end_time,
DATEDIFF_BIG(second,last_request_end_time,SYSDATETIME()) AS IdleSeconds
FROM sys.dm_exec_sessions
WHERE is_user_process=1 AND status=N'sleeping' AND open_transaction_count>0
ORDER BY last_request_end_time;These times use the server's time basis. A NULL last request end needs separate interpretation rather than an invented age. An open count establishes unfinished transaction context, not the scope or business value of that work. Capture another sample when the condition is intermittent. A single live view disappears as soon as the application commits or disconnects.
Connect Sleeping Sessions to Their Locks
sys.dm_tran_session_transactions maps transaction identifiers to sessions. Join it to the session inventory to inspect the mapping. Transaction counts in different monitoring views can differ because they count different contexts. Distributed work and multiple active result sets add complexity. Do not turn one count mismatch into an automatic corruption diagnosis.
SELECT s.session_id,s.login_name,t.transaction_id,
t.is_user_transaction,t.is_local,t.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_tran_session_transactions AS t ON t.session_id=s.session_id
WHERE s.status=N'sleeping' AND s.open_transaction_count>0;
SELECT l.request_session_id,l.resource_type,l.resource_database_id,
l.resource_associated_entity_id,l.request_mode,l.request_status,
l.request_owner_type,l.request_owner_id
FROM sys.dm_tran_locks AS l
WHERE EXISTS
(
SELECT 1 FROM sys.dm_exec_sessions AS s
WHERE s.session_id=l.request_session_id
AND s.status=N'sleeping' AND s.open_transaction_count>0
)
ORDER BY l.request_session_id,l.resource_type;Inspect granted transaction owned locks and the waiting requests they affect. Lock rows also represent other owners and resource types. An associated entity identifier is not universally a table object ID. Interpret it according to resource_type, and use the correct database metadata when resolving a key or page. A convincing table name attached to the wrong resource makes the response worse.
Read the Last Batch Without Calling It the Cause
The connection view retains most_recent_sql_handle. Apply sys.dm_exec_sql_text to that handle to retrieve the last submitted batch. It can be a commit attempt, a status query, or a procedure call rather than the original statement that acquired the lock. Keep that limitation visible. You need transaction history or application evidence to reconstruct earlier work.
SELECT s.session_id,s.login_name,s.host_name,s.program_name,
s.open_transaction_count,c.connect_time,c.client_net_address,
txt.text AS MostRecentBatch
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_connections AS c ON c.session_id=s.session_id
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS txt
WHERE s.status=N'sleeping' AND s.open_transaction_count>0;I save the batch with the session's connection details before changing anything. A later sample can show a different batch under the same session. Session IDs are reused after disconnects, so include connection and login times when preserving evidence. Multiple connection rows also deserve attention before assuming every returned batch represents an independent blocking session.
Find the Requests Blocked by Sleeping Sessions
sys.dm_exec_requests includes active waiting requests. The blocker itself can be absent from that view because it is sleeping. Join the blocking identifier to sessions instead. Show the waiting request's wait type and current wait duration. That duration is a current wait measure, not a complete history of how long the application has been unhappy.
SELECT r.session_id AS WaitingSession,r.blocking_session_id,
r.wait_type,r.wait_time,r.command,
b.status AS BlockerStatus,b.open_transaction_count AS BlockerTransactions,
b.login_name AS BlockerLogin,b.program_name AS BlockerProgram
FROM sys.dm_exec_requests AS r
LEFT JOIN sys.dm_exec_sessions AS b ON b.session_id=r.blocking_session_id
WHERE r.blocking_session_id>0;Walk longer blocking chains to the actual head when several sessions are involved. Do not terminate an intermediate waiter simply because it appears frequently. Negative blocking identifiers describe special cases rather than ordinary session IDs. Correlate lock resources and application behavior before assigning an owner. The busiest row in a monitoring grid is not automatically the guilty one.
Reproduce the Pattern in Two Windows
Create this table in a disposable database, then open two windows connected to that same database. Window A starts a transaction and changes one row. After the batch finishes, leave the window connected without committing. Its request ends while the transaction remains open. The monitoring queries can then inspect that session separately.
CREATE TABLE dbo.LockDemo
(ItemID int NOT NULL PRIMARY KEY,ItemValue int NOT NULL);
INSERT dbo.LockDemo VALUES(1,10);BEGIN TRAN;
UPDATE dbo.LockDemo SET ItemValue=11 WHERE ItemID=1;
SELECT @@SPID AS WindowASession,@@TRANCOUNT AS OpenTransactions;In window B, the locking read requests a shared lock even if the test database uses row versioned read committed behavior. Its lock timeout bounds the demonstration. The number is an input setting, not a reported measured wait. Keep window A's transaction open only for the rehearsal, then roll it back in that original window.
SET LOCK_TIMEOUT 5000;
BEGIN TRY
SELECT ItemID,ItemValue FROM dbo.LockDemo WITH(READCOMMITTEDLOCK) WHERE ItemID=1;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
SET LOCK_TIMEOUT -1;IF @@TRANCOUNT>0 ROLLBACK;Agree on the Response Before KILL
Contact the application owner with the session, transaction, lock, and batch evidence. Ask whether the application can complete or roll back its work cleanly. Confirm the current connection identity again before an approved termination. KILL rolls back unfinished transactional work and that rollback can take time. Removing a session from a list is not the same as finishing its recovery.
Do not promise immediate relief from a large rollback. Monitor its progress through the supported status checks and keep the service owner informed. Preserve the reason for the action. A long idle transaction can be holding important work, and a hurried kill can exchange blocking for a different outage. An unattended connection has no opinion about your maintenance window.
Repair the Transaction Lifecycle
Prevent recurring sleeping sessions with unfinished work by checking exception paths, canceled requests, timeout handling, and connections returned to a pool. Every path needs a deliberate transaction outcome. Use appropriate TRY CATCH and transaction state handling in server code, and explicit commit or rollback behavior in the application. Keep transactions away from user pauses and unrelated remote calls whenever the business operation allows it.
I verify the fix with the original workflow, including its failure case. Monitor sleeping sessions with open transactions over a representative period instead of declaring victory from one clean sample. The normal pool remains allowed to be idle. The unfinished work is what needs an owner, a bounded lifetime, and a reliable ending.
Related reading on this blog: SET XACT_ABORT ON: Stopping Timeouts From Leaving Open Transactions and Blocking Tree: Identifying Blocking Chain Using SQL Scripts.
An idle connection is not finished work, it is a session whose transaction still needs checking.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
