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

by youcantryreachingme   Last Updated November 09, 2018 00:06 AM - source

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)?

Related Questions

How we can find who killed sql server process?

Updated February 18, 2019 08:06 AM

Who killed MySQL?

Updated June 23, 2017 09:06 AM