Not able to kill SPID with KILL WITH STATUSONLY? That is because it does not kill anything. It reports on a rollback that is already running. People reach for it expecting a harder kill, get an error, and assume the session is stuck. The session is usually fine. It is undoing work, and that takes as long as it takes.

What the Two Commands Do
KILL 73 asks SQL Server to stop session 73 and undo whatever it had not committed.
KILL 73 WITH STATUSONLY asks a different question entirely: how is that rollback getting on. It starts nothing. It changes nothing. It reads a progress number.
The Error You Probably Got
Msg 6106, Level 16, State 1
Process ID 999 is not an active process ID.That is what you get when the session is already gone. The rollback finished, or the session never existed. Either way there is nothing left to report on, so this is a good error.
The other common reply is a plain statement that no rollback is in progress. Same meaning. You asked about work that is not happening.
Where the Real Answer Lives
Rather than guessing, look at the request:
SELECT session_id, status, command, percent_complete,
estimated_completion_time / 1000 AS seconds_left,
wait_type, wait_time, blocking_session_id
FROM sys.dm_exec_requests
WHERE session_id = 73;A session that is genuinely rolling back shows a command of KILLED/ROLLBACK and a percent_complete that climbs. If the number is moving, leave it alone. It will finish.
If the request has vanished from that view, the rollback is done and the session is closing. Nothing to do.
Why It Takes So Long
Undoing is not faster than doing, and it is often slower.
The original work may have run in parallel across several processors. A rollback runs single threaded. So a fifteen minute UPDATE can take considerably longer to undo, and that is normal rather than a fault.
A rollback is also not optional. SQL Server cannot leave half an UPDATE in the database, because then the database is wrong. This is the same promise that makes a transaction worth having, seen from the uncomfortable side.
When Percent Complete Does Not Move
Check what it is waiting on before assuming it is stuck.
SELECT r.session_id, r.status, r.command, r.wait_type, r.wait_time,
r.blocking_session_id, DB_NAME(r.database_id) AS db
FROM sys.dm_exec_requests AS r
WHERE r.session_id > 50
ORDER BY r.wait_time DESC;A wait on the log file means it is writing and the disk is busy. A blocking_session_id that is not zero means something else is in its way, which is rare during a rollback but does happen.
A rollback that truly stops moving for a long time, with no wait and no progress, is worth raising properly rather than working around.
What Not to Do
Do not run KILL again. It does nothing to a session already rolling back, and it makes people feel that something is broken when it is not.
Do not restart the service. This is the one that costs a morning. The rollback does not disappear when SQL Server stops. It carries on during recovery when the service comes back, and now the whole database is unavailable while it finishes instead of one session.
You have turned a problem affecting one connection into a problem affecting everybody, and made it no faster.
Watching Recovery If Somebody Already Restarted
SELECT name, state_desc, is_in_standby
FROM sys.databases
WHERE name = 'Sales';
EXEC xp_readerrorlog 0, 1, N'recovery', N'Sales';The error log reports recovery progress as a percentage during startup. That is the only honest estimate available at that point.
Stopping It Happening Again
Nearly every painful rollback started as one enormous statement. Update fifty million rows in a single UPDATE and you have built yourself a very long undo.
Do large changes in batches, committing each one. It takes longer in total and each batch is cheap to abandon. That is the whole trade, and on a production system it is worth making.
KILL WITH STATUSONLY is not a stronger kill, it is a progress report on a job that has to finish.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.





3 Comments. Leave new
Thanks for the information. It saved my day.
Some spid won’t kill in suspended state. How to kill it forcefully? after killing it it still in who2 list but in killed state. How to eliminate this.
I did a kill xxx with statusonly and was very impolitely told:
Msg 6120, Level 16, State 1, Line 55
Status report cannot be obtained. Rollback operation for Process ID 57 is not in progress.
What’s more doing an ‘exec sp_who2’ that spid does not show up. But, it does show up when querying sys.dm_exec_sessions.
The sad thing is I have to get these sessions disconnected so I can use a refreshed database that must use the same database name according to a vendor. Evidently there’s a ton of work involved to get their application to point to a different database.
If anyone has any insight as to what’s going on, I’d greatly appreciate some help!
Thank you!