A blocked system tempts you to KILL a session immediately. That command can release the problem, but a large transaction must be undone first, and rollback can take longer than the original work. Inspect the request and transaction before choosing the next move.
Identify the Blocking Session Before Any KILL
Start with the chain, not the session that complained loudest. sys.dm_exec_requests shows blocking_session_id for active requests. The head blocker can be sleeping with an open transaction and have no row in that view. Join session and transaction views, inspect the host, login, application, and last request, and contact the owner if time allows. Confirm that the session ID has not been reused between observation and action.
I have seen a team stop a blocked reader while the writer continued holding the same locks. The alert quieted briefly, then returned. Which session owns the lock that matters, and what business operation is it performing? Capture that answer before a disruptive command.
SELECT r.session_id, r.blocking_session_id, r.status,
r.command, r.percent_complete,
r.total_elapsed_time, r.wait_type,
s.login_name, s.host_name, s.program_name
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
ON s.session_id = r.session_id
WHERE s.is_user_process = 1
ORDER BY r.blocking_session_id DESC, r.session_id;The filter uses is_user_process because a session ID above 50 can still belong to a background task. A missing blocker row does not clear the session. Inspect open transactions and the most recent input buffer when the blocker is sleeping. A KILL against the wrong session consumes time and can create a second rollback without solving the first wait.
Measure the Transaction Already Written
sys.dm_tran_session_transactions maps a session to transaction IDs. sys.dm_tran_active_transactions gives a transaction start time, while sys.dm_tran_database_transactions shows when each database joined and how many log bytes the transaction has used there. A transaction touching multiple databases has one row per involved database. Read the totals as clues to undo work, not a stopwatch.
DECLARE @session_id int = 57;
SELECT st.session_id, at.transaction_id,
at.transaction_begin_time,
DB_NAME(dt.database_id) AS database_name,
dt.database_transaction_begin_time,
dt.database_transaction_log_bytes_used,
dt.database_transaction_log_bytes_reserved
FROM sys.dm_tran_session_transactions AS st
JOIN sys.dm_tran_active_transactions AS at
ON at.transaction_id = st.transaction_id
JOIN sys.dm_tran_database_transactions AS dt
ON dt.transaction_id = at.transaction_id
WHERE st.session_id = @session_id;Run this with the observed session ID and the required server-state permission. The bytes already logged are not a precise estimate of rollback duration. Log records vary in cost, concurrent work changes throughput, and an operation can have internal transaction behavior. Still, a transaction that has generated hundreds of gigabytes of log deserves a different decision from one that changed a handful of rows.
Read the Current Command in Context
A large DELETE can have millions of row changes to undo. An index rebuild has different logging and recovery behavior depending on options and phase. Look at command, percent_complete, waits, and text, then inspect the execution plan or the running maintenance job. percent_complete is populated for selected operations, including certain index and backup work; NULL or zero for an ordinary query does not mean no progress.
DECLARE @session_id int = 57;
SELECT r.session_id, r.command, r.percent_complete,
r.start_time, r.total_elapsed_time,
r.wait_type, r.wait_time, r.blocking_session_id,
t.text AS batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id = @session_id;A transaction could include earlier statements that are absent from the current request text. A sleeping session has no active request at all. Inspect transaction age and application logs before treating the displayed command as the complete unit of work. If the operation is close to a known finish, letting it complete can be faster than undoing it.
Compare Two Costs Before You KILL a Session
Ask how long the blocker is expected to run, what it protects, and what happens if it is canceled. An unattended batch that is safe to restart is different from a customer payment commit. Check available log space because rollback writes log records too. Check whether an Availability Group, replication, or downstream process is already behind. Record the decision and the session identity in the incident timeline.
The clock in an outage creates pressure to do something visible. I use a short checklist because a dramatic command is not always the fastest repair. If the session is idle in an open transaction, contact the application owner or use the approved incident procedure. If it is actively completing a huge operation, compare projected finish with undo and restart cost. There is no universal byte-to-minute formula.
KILL the Session Once, Then Watch Status
When the decision is made, recheck the session ID and transaction details, then issue KILL with the numeric ID. The command can return while rollback continues. Use WITH STATUSONLY for progress; it reports estimated rollback completion and time remaining when rollback is active. Do not keep issuing plain KILL, because a finished session ID could be assigned to another connection.
-- After confirming that 57 is still the intended session:
KILL 57;
GO
KILL 57 WITH STATUSONLY;The second statement is a status request, not another cancellation. If it says rollback is not in progress, the undo could have finished or the session was never rolling back. Check current sessions and the original blocking chain. A changing percentage is useful, but its time estimate can move as the system load changes.
Close the Incident With Evidence
Capture the start and end of rollback, log growth, blocking count, and application recovery. Verify the original transaction did not leave the business workflow half-complete. A canceled statement rolls back its transaction according to its boundaries, but outside systems and prior commits have their own state. Ask the application owner to reconcile those effects.
For the next occurrence, shorten the transaction, batch a large DELETE, or schedule rebuilds with the right online and resumable options for the edition and version. Put a timeout and an escalation path in the runbook. The best KILL decision is made with enough evidence to explain both the action and its cost afterward.
Make the Log Estimate Explainable
The log byte count is a measure of records generated by that transaction in a database, not a count of rows left to undo. A DELETE with cascading changes, index maintenance, and triggers can generate much more log than a simple row count suggests. Check whether the transaction is still adding log between two observations. If it is, waiting for the operation to finish has a different risk than allowing a stalled transaction to hold locks indefinitely.
For an index rebuild, identify whether the operation is online, offline, or resumable and where it is in its lifecycle. Some work can be paused through its supported command instead of canceled. Do not assume a request percentage predicts rollback time; it describes forward progress for that command when available. Save the before sample so the incident review can compare the eventual undo duration with the decision made under pressure.
Related reading on this blog: Blocking Tree: Identifying Blocking Chain Using SQL Scripts and Getting Started with Accelerated Database Recovery: Instant Rollback.
KILL is not instant relief, it is a rollback whose cost you must assess first.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
