SQL Server Sleeping Session Blocking: Find the Open Transaction

DBA PARK may earn a commission from purchases through links in this article, at no extra cost to you.

A SQL Server session can be sleeping and still block other sessions. Sleeping describes whether the session is executing a request; it does not prove that its transaction has ended. Start with the blocked request, identify its blocker, and check that session’s transaction state before choosing a remedy. [1][2]

This guide focuses on ordinary user-session blocking in SQL Server 2016 and later. Run the diagnostic queries from a separate administrative connection. They read server state; the termination example later in the article changes it. Azure SQL Database has different visibility and permission rules.

Why a sleeping session can hold locks

Consider an application that begins a transaction, updates a row, and then waits for another application operation without committing or rolling back. The update request has finished, but the transaction remains open. A conflicting request from another connection can wait for that transaction.

An application timeout or cancellation can produce a similar symptom if transaction cleanup is missing. Do not infer that the transaction rolled back just because the application displayed a timeout. [1]

The distinction matters when choosing a DMV:

Object	What it helps establish
`sys.dm_exec_requests`	Which requests are waiting and their reported blockers
`sys.dm_exec_sessions`	Whether a blocker still exists, its session status, and open transaction count
`sys.dm_exec_input_buffer`	The submitted command currently available in the session’s input buffer
Transaction DMVs	Which transactions and databases are associated with the session

1. Find blocked requests without losing sleeping blockers

An inner join from a blocked request to the blocker’s request can hide a blocker that has no active request. Join the blocker to sys.dm_exec_sessions instead. [2][3]

SELECT
    SYSDATETIME() AS captured_at,
    r.session_id AS blocked_session_id,
    r.request_id,
    DB_NAME(r.database_id) AS blocked_request_database,
    r.wait_type,
    r.wait_time AS current_wait_ms,
    r.wait_resource,
    r.blocking_session_id,
    b.login_time AS blocker_login_time,
    b.status AS blocker_session_status,
    b.open_transaction_count AS blocker_open_transactions,
    b.last_request_end_time AS blocker_last_request_end_time,
    b.login_name AS blocker_login_name,
    b.host_name AS blocker_host_name,
    b.program_name AS blocker_program_name
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
  AND r.session_id <> @@SPID
ORDER BY r.wait_time DESC;

Read this as a list of direct blocking relationships, not a complete historical blocking tree. If the blocker is also blocked, follow its blocking_session_id until you reach the head of that chain. Negative blocker IDs are deliberately outside this query; they describe special conditions rather than ordinary user sessions. Parallel requests can also require task-level investigation. [3]

A useful investigation target has all three signals: other requests name it as their blocker, its session status is sleeping, and its open transaction count is positive. Sleeping alone is not a reason to terminate a connection. Capture another snapshot to check whether the condition persists. [1][2]

For SQL Server 2016–2019, instance-wide diagnostics generally require VIEW SERVER STATE. For SQL Server 2022 and later, the DMVs and function used here require VIEW SERVER PERFORMANCE STATE for this visibility. Ask your DBA for the appropriate access; incomplete visibility can hide the blocker. [2][3][4][5][6]

Figure 1. Two waiting requests, one sleeping blocker
Illustrative output — invented values, not a captured SQL Server result. Selected columns shown.

blocked_session_id	wait_type	current_wait_ms	blocking_session_id	blocker_session_status	blocker_open_transactions
64	LCK_M_X	45000	57	sleeping	1
72	LCK_M_X	18000	57	sleeping	1

How to read this: 64 and 72 are waiting; 57 is the blocker. The repeated 57 identifies the shared owner to investigate. The combination of sleeping and one open transaction explains why an idle-looking session deserves attention. The wait values are milliseconds: 45 seconds and 18 seconds. Confirm that 57 is not itself blocked before calling it the head blocker.

2. Inspect the blocker’s input buffer

Use the actual positive session ID from your capture. The variable defaults to NULL so this template does not select an arbitrary production session.

DECLARE @blocker_session_id smallint = NULL; -- Set the captured session ID.

SELECT
    s.session_id,
    s.login_time,
    s.status,
    s.open_transaction_count,
    s.last_request_start_time,
    s.last_request_end_time,
    ib.event_type,
    ib.event_info AS submitted_command
FROM sys.dm_exec_sessions AS s
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE s.session_id = @blocker_session_id;

The input buffer exposes submitted text, not a complete transaction history. A procedure call or a later statement might appear instead of the earlier statement that acquired the conflicting lock. Save the result with the capture time and correlate it with application logs or an appropriately scoped Extended Events capture. Protect captured SQL text because it can contain business data. [4]

The client supplies host_name, so treat it as a routing clue for finding the application owner, not proof of identity. Also, last_request_end_time measures the last request completion; it is not the transaction start time. [2]

Figure 2. Inspect session 57, not the waiting sessions
Illustrative output — invented values, not a captured SQL Server result. Selected columns shown.

session_id	status	open_transaction_count	submitted_command
57	sleeping	1	`BEGIN TRAN; UPDATE dbo.Inventory SET quantity = quantity - 1 WHERE item_id = 1001;`

How to read this: The submitted batch starts a transaction and contains no COMMIT or ROLLBACK. Together with the open transaction count, this is consistent with the example. Real input buffers may show a later command instead; they are not a full transaction history. Set @blocker_session_id to 57 in the diagnostic query for this scenario only.

3. Identify the transaction’s databases

The database on a blocked request tells you where that request is executing. To inspect the blocker’s transaction participation, join session transactions to database transactions:

DECLARE @blocker_session_id smallint = NULL; -- Set the captured session ID.

SELECT
    st.session_id,
    st.transaction_id,
    st.is_user_transaction,
    st.is_local,
    DB_NAME(dt.database_id) AS transaction_database,
    dt.database_transaction_begin_time,
    dt.database_transaction_state,
    dt.database_transaction_log_bytes_used
FROM sys.dm_tran_session_transactions AS st
JOIN sys.dm_tran_database_transactions AS dt
    ON dt.transaction_id = st.transaction_id
WHERE st.session_id = @blocker_session_id
ORDER BY dt.database_transaction_begin_time, dt.database_id;

Do not assume that a session corresponds to exactly one result row. MARS, bound sessions, and distributed transactions can complicate the session-to-transaction mapping. Open transaction counts in different DMVs can differ because they are recorded differently. [5]

database_transaction_begin_time describes the database transaction, including the timing of its first log record; it is not necessarily when the application originally issued BEGIN TRANSACTION. Log bytes used help describe the work represented in that database, but do not predict an exact rollback duration. These rows show transaction participation, not proof that every listed database contains the blocking resource. [6]

Figure 3. Connect the session to its database transaction
Illustrative output — invented values, not a captured SQL Server result. Selected columns shown.

session_id	transaction_id	is_user_transaction	is_local	transaction_database	database_transaction_log_bytes_used
57	482913	1	1	BlockingDemo	4096

How to read this: Session 57 maps to transaction 482913 in BlockingDemo. This illustrative row describes a local user transaction. The 4,096 log bytes are not a lock count or a rollback-time estimate. Compare the database and transaction evidence with the blocked request; do not assume one row per session in every workload.

4. Choose a remedy after identifying the owner

Finding	Next action
Application still owns an unfinished business operation	Ask its owner to complete or roll back the transaction through the owning connection, as appropriate
Timeout or cancel preceded the incident	Inspect cancellation handling and transaction cleanup in the application
Persistent blocker must be terminated to restore service	Capture evidence, confirm the current session identity, and assess rollback impact before using `KILL`
Session is idle but no blocked requests point to it	Continue diagnosis instead of treating idle status as the cause

Query timeouts are generally enforced by the client’s command timeout setting. Collect the application’s timeout configuration and timestamps; an Extended Events attention event can help correlate cancellation with server activity. Increasing the timeout may change the symptom without explaining why the request waited. [7]

If termination is the selected incident response, replace the placeholder with the verified session ID. These examples are intentionally commented out:

-- Terminates the selected connection and initiates required rollback:
-- KILL <verified_session_id>;

-- Reports rollback progress after a previous KILL:
-- KILL <verified_session_id> WITH STATUSONLY;

KILL can take time when substantial work must be undone. WITH STATUSONLY reports an existing rollback; it does not start one. Recheck identity immediately before termination because SQL Server can reuse session IDs after connections end. Do not repeat a plain KILL command as a progress check. [8]

5. Verify recovery and prevent recurrence

Rerun the first query and confirm that the affected requests progress. Ask the application owner to verify the failed business operation and check whether a new session from the same application recreates the pattern.

For the incident record, preserve the capture time, session ID and login time, submitted command, transaction/database rows, application timeout, chosen action, and observed outcome. These are the inputs needed to distinguish a one-off abandoned operation from a repeatable application defect.

Review transaction boundaries with the development team: where the transaction begins, which code paths finish it, and what happens during cancellation, exceptions, and retries. Keep external calls and user interaction outside an open database transaction where the business design permits. [1]

Frequently asked questions

Why is the sleeping blocker missing from sys.dm_exec_requests?

That DMV describes requests. A session without an active request can still appear in sys.dm_exec_sessions; use the session row when investigating an idle blocker. [2][3]

Can I commit the transaction from another SSMS window?

For an ordinary local transaction owned by an application session, issuing COMMIT in your separate administrative session does not finish the application’s transaction. Have the application resolve it on its owning connection, or evaluate termination and rollback as an incident action. [1][8]

Does an empty result mean blocking never happened?

No. These queries inspect current state. If the incident already cleared, correlate application timestamps with previously collected monitoring or Extended Events data. [3][7]

Continue learning: For guided practice beyond this fix, browse Udemy and search for SQL Server performance tuning courses with exercises on waits, blocking, and execution plans. For interactive practice with transactions and error handling in SQL Server, explore DataCamp.

Related DBA PARK guides

Essential DMV Queries for SQL Server Performance Monitoring — expand the investigation to CPU, I/O, and other waits.
SQL Server Troubleshooting and Performance Guides — find related operational workflows.

References

Technical references checked September 8, 2026. The diagnostic SQL was reviewed against Microsoft’s documented objects; it has not been executed against a SQL Server instance for this article.