KILL WITH STATUSONLY: How Long Will This Rollback Take?

Someone ended a session, but its transaction is still rolling back. KILL WITH STATUSONLY reports the rollback's current estimate without killing anything again. Use that evidence to manage the wait rather than turning an unfinished cleanup into a wider outage.

Separate Ending a Session from Undoing Its Work

KILL tells SQL Server to end the selected user session. Uncommitted changes still need the engine's rollback and recovery mechanisms. Removing the connection does not authorize SQL Server to abandon transaction consistency.

With traditional recovery, undo can require substantial logged work. Its duration depends on the changes, storage, resource pressure, and current conditions. It can rival the original operation's duration, but there is no universal one-to-one timing formula.

I explain that distinction before ending a large transaction. I also check whether a smaller intervention can resolve the original blocking problem. Killing the session is an operational decision with consequences, not an extra-strength cancel button.

Do not use production data for the demonstration below. The sample intentionally creates a sizable open transaction and then disconnects its owner. Confirm log space and keep the experiment within your test environment's resource budget.

Create an Identifiable Open Transaction

Run this setup in connection A inside a disposable test database. It generates a declared sample population without asserting an observed execution time. The table name is dedicated to this demonstration and should not be shared with application data.

IF OBJECT_ID(N'dbo.RollbackDemo', N'U') IS NOT NULL
    THROW 51000, 'Remove or rename the previous sample before setup.', 1;
CREATE TABLE dbo.RollbackDemo
(
    ItemId int NOT NULL PRIMARY KEY,
    Payload char(200) NOT NULL
);
;WITH Digits AS
(
    SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS d(n)
), Numbers AS
(
    SELECT a.n + 10*b.n + 100*c.n + 1000*d.n + 10000*e.n AS n
    FROM Digits AS a CROSS JOIN Digits AS b
    CROSS JOIN Digits AS c CROSS JOIN Digits AS d CROSS JOIN Digits AS e
)
INSERT dbo.RollbackDemo
SELECT n, REPLICATE('a', 200) FROM Numbers;
SELECT @@SPID AS DemonstrationSession, DB_NAME() AS DemonstrationDatabase;
BEGIN TRANSACTION;
UPDATE dbo.RollbackDemo
SET Payload = REPLICATE('b', 200);
SELECT @@TRANCOUNT AS OpenTransactions;
-- Leave this transaction open for the controlled connection B test.

Record the returned session identifier and database name. Do not commit this demonstration transaction if the intended experiment is rollback. Connection B needs to inspect the exact connection before issuing the initial KILL.

The update can finish before you switch windows, while its transaction remains open. That is still a valid transaction to roll back. On a fast or ADR-enabled database, rollback can also finish before a status observation catches it.

Verify the Target before the First KILL

Replace the example identifier in the following inspection with A's actual identifier. Compare login, connection time, database, and visible command details. A session number alone is insufficient because SQL Server can reuse it after a connection ends.

DECLARE @TargetSession int = 52; -- Replace with connection A's identifier.
SELECT s.session_id, s.login_name, s.host_name, s.program_name,
       s.open_transaction_count, c.connect_time,
       r.command, r.status, r.database_id, r.wait_type,
       r.blocking_session_id
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_connections AS c ON c.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
WHERE s.session_id = @TargetSession;

An idle session with an open transaction can have no current request row. That explains the left joins rather than proving the transaction disappeared. Check the session and transaction state before drawing a conclusion from an empty request view.

After verifying A, issue KILL once from B using the confirmed identifier. The following literal is an example, not an instruction to terminate whichever session currently happens to be 52. Replace it only after completing the inspection.

-- Run once, only for the verified demonstration connection.
KILL 52;

SQL Server requires ALTER ANY CONNECTION for KILL, with administrative roles supplying that authority. Monitoring access does not automatically grant termination access. Your operational process should distinguish those responsibilities explicitly.

Request STATUSONLY without Repeating Termination

Use the same confirmed identifier with STATUSONLY after the initial KILL. This form reports a rollback caused by the earlier termination. The reply names the session and gives an estimated completion percentage and remaining seconds. It does not restart rollback, cancel another session, or accelerate the remaining work.

KILL 52 WITH STATUSONLY;

The message contains estimated completion percentage and estimated remaining seconds. Treat both as changing estimates, not promises. Sampling over a reasonable interval gives more context than reacting to one apparently unchanged percentage.

If rollback is no longer in progress, SQL Server reports error 6120 for this status request. That can mean rollback finished before the check, or no matching rollback existed. Verify current state rather than reading that message as a failed rollback.

Repeated plain KILL is dangerous after the original session finishes. Its identifier can be assigned to an unrelated connection before your next command runs. STATUSONLY avoids turning a routine progress check into termination of that new connection.

Inspect Resource Conditions During Traditional Rollback

Inspect the active request while rollback remains visible. The following query reports current waits and counters for your confirmed target. These counters provide context; they do not establish a fixed remaining duration.

DECLARE @TargetSession int = 52; -- Replace with the verified identifier.
SELECT session_id, command, status, percent_complete,
       estimated_completion_time, wait_type, wait_time,
       blocking_session_id, cpu_time, reads, writes
FROM sys.dm_exec_requests
WHERE session_id = @TargetSession;

Look for storage delays, blocking, or resource pressure that explain slow progress. Preserve observations and timestamps when escalating the issue. Do not repeatedly generate additional heavy diagnostic work against an already stressed server.

Restarting the service does not erase the need to recover uncommitted work. It can turn one session's problem into database recovery and wider downtime. A restart requires a separate, evidence-based operational reason rather than impatience with an estimate.

After rollback completes, reconnect and verify that the sample payload remains the original value. Do not check it with dirty reads during rollback and treat that as final evidence. Remove the sample table only after confirming the transaction is finished.

Compare STATUSONLY Results under Accelerated Database Recovery

SQL Server 2019 introduced accelerated database recovery, configured per database. SQL Server defaults differ from Azure SQL Database and Managed Instance, where ADR is always enabled. Read the current database setting before comparing rollback behavior between environments.

SELECT name, is_accelerated_database_recovery_on
FROM sys.databases
WHERE database_id = DB_ID();
SELECT database_id, persistent_version_store_size_kb
FROM sys.dm_tran_persistent_version_store_stats
WHERE database_id = DB_ID();

ADR uses persisted row versions and logical revert to accelerate transaction rollback. Versioned undo and background cleanup change the relationship between user-visible rollback and physical reclamation. Remaining persistent version-store cleanup is not the same as an active traditional rollback.

Do not enable ADR in the middle of this experiment as an emergency shortcut. Changing its setting requires planning, appropriate locking conditions, and storage capacity review. Test the actual workload and monitor its version-store behavior before deployment.

I check recovery mode and ADR state when rollback timing surprises a team. I also distinguish a completed abort from cleanup still consuming space. SQL Server's housekeeping does not always finish when the application's drama ends.

Can you identify the original connection and explain what resource currently limits its rollback? If not, collect that evidence before intervening again. STATUSONLY supports calm observation, while disciplined transaction sizing reduces the next emergency.

Related reading on this blog: Before You KILL a Session: Estimating the Rollback Cost and Getting Started with Accelerated Database Recovery: Instant Rollback.

A killed session is not abandoned work, it is a transaction the engine must safely undo.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.