What Is KILLED/ROLLBACK In SQL Server?

The KILLED/ROLLBACK is a common condition that database developers/DBAs come across. It will be a pounding situation for many DBAs in case a SPID goes into a KILLED/ROLLBACK in an outage window. This is because the KILLED/ROLLBACK can take enough time to roll back the killed session.

So, what is the KILLED/ROLLBACK state? How to get things back to normal?

The KILLED/ROLLBACK is a situation where a DBA kills a SPID to end a process, but SQL reverses the work done. This mainly happens when a session is hung or takes a long execution time. Once the status of the command changes to KILLED/ROLLBACK, the SQL does the real rollback process. In most cases, the KILLED/ROLLBACK processes get rolled back completely. But in rare cases, the SQL might not have real control over the command. In such cases, the KILLED/ROLLBACK SPID can stay in the same state forever.

Query to check the SPID in KILLED/ROLLBACK

select spid,
kpid,
cmd,
loginame,
lastwaittype
from sys.sysprocesses

Solution

Wait for the SPID to completely rollback. If it is related to a hung backup job, then you can restart the SQL Backup Services. This should fix your situation
If the SPID has still not rolled back, then restart the SQL Service.

Note: As per my experience, the rollback situations are time-consuming. It takes more CPU, Memory, and IO. If you ever killed a SPID that has gone into a ROLLBACK situation, then keep a note when you get into a similar situation as you did now. This might help some of your time.

Bottomline

The KILLED/ROLLBACK is a real rollback situation where SQL is trying to get the database to a consistent state. However, if the SQL doesn’t have enough control over the process, the SPID can be in KILLED/ROLLBACK forever. A restart of SQL Backup Service/SQL Server Services is postponing the SPID’s rollback. The database becomes available once the recovery step runs after the SQL Service restart.