SQL Server sleeping head blocker: the idle session holding a lock and an open transaction

The most dangerous blocking shape in SQL Server does not look active. Sessions pile up behind a head blocker, batch requests stall, and transactions per second drop. CPU and I/O are idle. The engine looks healthy on resource metrics, but queries are timing out.

When you query sys.dm_exec_requests for the blocker, you find nothing. The session_id that everyone is waiting on does not appear in the requests DMV because the head blocker has no active request. It is sleeping. It is, however, holding locks under an open transaction that never committed, never rolled back, and never will on its own.

This is almost always an application bug or an abandoned interactive session. A connection with an open transaction was returned to the pool, a query timeout fired without XACT_ABORT ON, or someone left an SSMS window open with a BEGIN TRAN and walked away. The locks will not release until the transaction ends. The chain will not clear until the locks release. Killing the session triggers a rollback that may take as long as the original work.

What this means

Locks in SQL Server are tied to transactions. A session that takes a lock under a transaction holds it until the transaction commits or rolls back. If the session finishes its current batch without ending the transaction, SQL Server keeps the transaction open and keeps the locks. The session then goes to sleep waiting for the next command from the client. From the engine’s perspective, the session is idle. From the perspective of every session waiting on those locks, the session is the problem.

An active head blocker (a session running a long query that takes locks) eventually finishes its work, releases the locks, and the chain clears. A sleeping head blocker has nothing left to do. It will not finish. It is waiting for the application or the user to send the next command, and that next command may never come.

The blocking DMVs reflect this asymmetry. sys.dm_exec_requests shows blocked sessions but no row for the head blocker itself. sys.dm_exec_sessions shows the head blocker with status = 'sleeping'. That combination is the signature. The longer it persists and the more sessions back up behind it, the closer you are to worker thread exhaustion: new connections cannot be serviced and the instance appears unresponsive even though CPU and I/O are both low.

flowchart TD
    A[Blocked sessions in dm_exec_requests] --> B{Head blocker row in dm_exec_requests?}
    B -- Yes --> C[Active blocker: long query or escalation]
    B -- No --> D[Suspect sleeping head blocker]
    D --> E{Open transaction on suspect session?}
    E -- No --> F[Investigate other lock sources]
    E -- Yes --> G[Sleeping head blocker confirmed]
    G --> H[KILL triggers rollback]
    H --> I{ADR enabled on this database?}
    I -- Yes --> J[Rollback near-instant on SQL Server 2019+]
    I -- No --> K[Rollback may take long - monitor with STATUSONLY]

Common causes

CauseWhat it looks likeFirst thing to check
Connection returned to pool mid-transactionprogram_name is the driver (.Net SqlClient, JDBC, HikariCP); host_name is an app serverApplication code around transaction boundaries and Dispose/Close patterns
Query timeout without XACT_ABORT ONApplication logged a timeout exception but the session is still on the instance holding locksSET XACT_ABORT in stored procedure definitions and connection settings
Abandoned SSMS sessionprogram_name = 'Microsoft SQL Server Management Studio', host_name is a workstationWho has SSMS open against this instance right now
ORM SaveChanges delayed under app-tier pressureHead blocker from the app server, transaction begin and commit split across requestsApp server CPU, GC, and request logs around the time blocking started
SET IMPLICIT_TRANSACTIONS ON left enabledSession opened an implicit transaction on a SELECT and never committedConnection settings and session-level SET options

Quick checks

These are read-only and safe to run on a live instance.

-- Find current blocking chains and head blockers
SELECT
    r.session_id AS blocked_session,
    r.blocking_session_id AS blocker,
    r.wait_type,
    r.wait_time / 1000 AS wait_seconds,
    DB_NAME(r.database_id) AS database_name,
    t.text AS blocked_query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

-- Identify head blockers (sessions others wait on but that are not blocked themselves)
SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    s.last_request_start_time,
    s.last_request_end_time,
    t.text AS last_query_text,
    (SELECT COUNT(*) FROM sys.dm_exec_requests r2
     WHERE r2.blocking_session_id = s.session_id) AS sessions_blocked
FROM sys.dm_exec_sessions s
CROSS APPLY sys.dm_exec_sql_text(s.most_recent_sql_handle) t
WHERE s.session_id IN (
    SELECT DISTINCT blocking_session_id FROM sys.dm_exec_requests
    WHERE blocking_session_id <> 0
)
AND s.session_id NOT IN (
    SELECT session_id FROM sys.dm_exec_requests
    WHERE blocking_session_id <> 0
)
ORDER BY sessions_blocked DESC;

The signature of the pattern: the head blocker has status = 'sleeping', a stale last_request_end_time (often minutes or hours in the past), and a large sessions_blocked count.

-- Confirm there is an open transaction on the suspect session
DBCC OPENTRAN;

DBCC OPENTRAN returns the oldest active transaction per database. If the suspect session’s SPID matches the output, you have confirmation. Note that DBCC OPENTRAN shows the oldest transaction per database and may miss read-only or snapshot transactions.

-- Find sleeping sessions with open transactions
SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.last_request_end_time,
    tst.open_transaction_count
FROM sys.dm_exec_sessions s
JOIN sys.dm_tran_session_transactions tst ON s.session_id = tst.session_id
WHERE s.status = 'sleeping'
  AND tst.open_transaction_count > 0
ORDER BY s.last_request_end_time;

Sessions in this list that have not made a request in minutes are the candidates for a sleeping head blocker. A sleeping session with no open transaction is harmless; the open_transaction_count > 0 filter is what matters.

How to diagnose it

  1. Confirm the head blocker is sleeping. The head blocker query above is the source of truth. If status is anything other than sleeping, you have an active blocker and a different remediation path.

  2. Confirm an open transaction. Cross-reference the SPID against DBCC OPENTRAN output, or use the sys.dm_tran_session_transactions join. The open_transaction_count column should be greater than zero.

  3. Capture the input buffer. The most_recent_sql_handle from the head blocker query may return NULL when the offending batch has aged out of cache. Try:

-- Get the last statement the head blocker submitted
SELECT event_info
FROM sys.dm_exec_input_buffer(<session_id>, NULL);

A NULL or empty input buffer is common with this pattern. The transaction was likely opened by an earlier batch that has since been cleaned from the cache.

  1. Inspect what locks are held. The blocked sessions’ wait types and resources tell you what the head blocker is pinning. LCK_M_S, LCK_M_X, LCK_M_U, and LCK_M_SCH_M map to shared, exclusive, update, and schema-modification locks respectively.

  2. Quantify the impact before acting. Count blocked sessions, note the oldest wait, and check whether transactions per second have dropped relative to batch requests per second. This tells you whether to kill now or wait for the application owner.

  3. Watch for the cascade into worker exhaustion. If active workers are climbing toward max_workers_count or THREADPOOL waits have started appearing in sys.dm_os_wait_stats, you are minutes from the instance refusing new connections. The Dedicated Admin Connection (DAC) bypasses normal connection limits and is your fallback when normal connections fail.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
Blocking chain depth and oldest waitQuantifies user-visible impactChain > 5 sessions, oldest wait > 60 seconds, head blocker sleeping
Transactions/sec vs batch requests/sec ratioQueries are arriving but not committingRatio dropping while batch requests stay flat or rise
LCK_M_* wait types in sys.dm_os_wait_statsAggregate lock pressureLock waits climbing into the top five waits by total wait time
THREADPOOL waits in sys.dm_os_wait_statsCascade approaching worker exhaustionAny sustained nonzero value
Worker thread utilizationPrelude to system unresponsivenessActive workers sustained above 80% of max_workers_count
Batch requests/sec trendThroughput drop signals user impactSustained 0.5x baseline or worse with no upstream explanation
log_reuse_wait_desc = ACTIVE_TRANSACTIONSide-effect of long open transactionsLong-running transaction is also blocking log truncation

Fixes

Kill the session after impact assessment

When the head blocker is sleeping with an open transaction and the blocking chain is causing throughput impact, the only path to immediate relief is KILL. This is not free.

-- Terminate the session - this triggers rollback
KILL <session_id>;

Rollback duration is roughly proportional to the work the transaction has done, not the time it has been open. A transaction that did 30 seconds of heavy updates can take 30 seconds or longer to roll back. The session shows KILLED/ROLLBACK during this period. Monitor progress with:

-- Watch rollback progress without affecting it
KILL <session_id> WITH STATUSONLY;

Killing is the right call when blocked sessions are accumulating and the application owner cannot be reached. Killing is the wrong call when the transaction has done very large modifications on a database without Accelerated Database Recovery enabled and the rollback will likely outlast the original blocking impact. Always check sys.dm_tran_session_transactions before killing a long-running writer.

On SQL Server 2017 CU24, 2019 CU8, and 2016 SP2 CU15 and later, a bug that could leave killed sessions stuck indefinitely in rollback waiting on TRANSACTION_MUTEX or XACT_OWN_TRANSACTION is fixed. If you are running older builds, treat KILL on a long-running transaction with extra caution.

Enable Accelerated Database Recovery (SQL Server 2019+) for faster rollback

ADR fundamentally changes rollback behavior. With ADR enabled, rollback is near-instantaneous regardless of transaction size. A KILL against a sleeping head blocker on an ADR-enabled database completes in seconds rather than minutes or hours.

ADR is available in SQL Server 2019 and later, Azure SQL Database, and Azure SQL Managed Instance. It is not available in SQL Server 2017 or earlier. Enabling ADR on an existing database has overhead in the form of a persistent version store and should be tested, but for OLTP workloads where this pattern recurs, it converts a multi-hour rollback risk into a non-event.

Fix the application’s transaction handling

The permanent fix is in the application tier.

  • Set XACT_ABORT ON for every stored procedure and every connection that takes transactions. With XACT_ABORT ON, a timeout or cancel rolls back the transaction automatically instead of leaving it open on a sleeping session. This is the single highest-leverage change.
  • Ensure connection-pool dispose paths always roll back. In .NET, wrap SqlConnection and SqlTransaction in using blocks, or use TransactionScope correctly. In JDBC, pair setAutoCommit(false) with explicit commit() or rollback() in a finally block.
  • Configure the pool’s connection-validation query to detect and clean up leaked transactions where the pool supports it. A query like IF @@TRANCOUNT > 0 ROLLBACK; run when a connection is returned to the pool catches the common case.
  • Investigate ORM patterns like Entity Framework’s SaveChanges that hold transactions open across the request. Under app-tier CPU or GC pressure, the commit is delayed and the session on SQL Server appears sleeping with an open transaction. The root cause is the app tier, not the database.

Walk away from abandoned SSMS sessions

For interactive sessions, the fix is procedural. Do not leave BEGIN TRAN uncommitted in SSMS. Use SET XACT_ABORT ON in query windows so a closed window rolls back instead of orphaning the transaction. Configure idle session timeouts so abandoned SSMS windows cannot hold locks indefinitely.

Prevention

  • Make SET XACT_ABORT ON the default in every stored procedure template and every application connection string.
  • Wrap every transaction in deterministic rollback-on-error patterns at the application layer. Treat uncaught exceptions as rollback triggers.
  • Add a connection-validation query to your pool configuration that includes IF @@TRANCOUNT > 0 ROLLBACK.
  • Schedule periodic checks of sys.dm_tran_session_transactions joined with sys.dm_exec_sessions for sessions where status = 'sleeping', open_transaction_count > 0, and last_request_end_time is older than a threshold. Alert on it.
  • Enable ADR on SQL Server 2019+ OLTP databases where the pattern recurs.
  • Audit application code that holds transactions across network calls or external dependencies. Those calls are the most common source of sleeping sessions with open transactions.

How Netdata helps

  • The blocking chain and head blocker queries run at per-second cadence and persist over time. A sleeping head blocker is hard to miss when the chart shows the same SPID blocking dozens of sessions for minutes.
  • Per-second wait statistics surface the LCK_M_* spike as the chain forms, before it cascades into THREADPOOL waits and worker exhaustion.
  • Correlating batch requests per second (still arriving), transactions per second (dropping), and worker thread utilization gives a fast read on whether you are looking at a sleeping head blocker versus an active slow query.
  • Trend charts of LCK_M_* wait time as a fraction of total waits make regression visible. When the ratio climbs after a deploy, an application code change is the likely cause.
  • Persistent history lets you reconstruct the timeline: when the sleeping session started, how long it held locks, and what else was running on the instance at the same time.

Netdata’s Microsoft SQL Server monitoring brings these signals together at per-second resolution.