Sample Header Ad - 728x90

How to immediately terminate a SPID with a very long rollback time estimated?

2 votes
1 answer
3563 views
I have a script to restore a database from a backup. Prior to the restore command, it has: set single_user with rollback immediate The script seemed to be hanging here. I used sp_who2 to find out a SPID number 70 was in ROLLBACK state. I then used kill 70 with statusonly to obtain estimated time remaining - and it translates to more than 20,000 hours. How can I arrest this rollback, even if it means the DB ends up in a transactionally inconsistent state? (I don't really care, as I just want to restore it from a backup.) Is my only option to shut down the server (and impact other DBs on that server)?
Asked by youcantryreachingme (1655 rep)
Nov 8, 2018, 11:43 PM
Last activity: Mar 7, 2019, 04:06 AM