Rollback not completing.

Question

Greetings.

I killed a spid 8 hours ago and it's still not done. Stuck in KILLED/ROLLBACK state, the wait_type is mostly PAGEIOLATCH_EX, the Reads/ Writes are still going up, and the sproc was from a user DB, but the statement it's been stuck on is in tempDB due to temp tables.

What's really annoying is the query below still shows 0% complete.

Where so I go from here?

select command,percent_complete,'elapsed'=total_elapsed_time/60000.0,'to complete' = estimated_completion_time /60000.0  from sys.dm_exec_requests where  
command like '%ROLLBACK%'  

Answer 1

There is not much you can do, you have to wait until the rollback finished.
Even if you restart SQL Server, the rollback process will continue on startup.

Answer 2

Some additions to the other posts:

• The percent_complete column is only maintained for certain commands like BACKUP/RESTORE, so it does not really say anything.
• If you kill a process that is stuck with something outside SQL Server, the KILL has no effect. A process can only discover that it has been killed if it yields in SQL Server co-operative multi-tasking model. Now, since you talk about PAGEIOLATCH_EX waits, I don't think this is the issue here. Had you seen PREEMPTIVE_xxx waits or OLEDB waits, it would be different. But I mention it, nevertheless.
• I have certainly encountered situations where restarting SQL Server has resolved situations like this, because instead of rollback, there was recovery which proved to be faster. But that was many years and versions ago since last time I had to do this, so I cannot vouch for that it is still true. If you restart the service, the rollback will not start over. But the database may be become entirely inaccessible until the rollback has completed.

Answer 3

Hi @chrisrdba
Try this query which can tell us more about the SPID and from where the connection might have originated.

SELECT spid,kpid,login_time,last_batch,status,hostname,nt_username,loginame,hostprocess,cpu,memusage,physical_io  
FROM sys.sysprocesses  
WHERE cmd = 'KILLED/ROLLBACK'  

Also, you could restart SQL Service if it was killed and doing nothing.

Refer to this blog for more details: SQL SERVER – SPID is KILLED/ROLLBACK state. What to Do Next?

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.