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

Question

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

Answer 1

Let that thing rollback. There's nothing you can do to stop it or speed it up. If you shut down the server or "trip" on the power cord, the rollback will just start back up again when you turn the server back on.

In any case, if you can restore as a different name, you don't need to rename the data file. First, see what files you have:

RESTORE FILELISTONLY FROM DISK = 'C:\path\file.bak';

It will indicate two or more files. You need to map those into the below. To find out what the current data path is, in case the backup is from a different instance, you can grab it from:

EXEC master.sys.sp_helpfile;

Then the restore command is like this:

RESTORE DATABASE [new_name_here]
  FROM DISK = 'C:\path\file.bak'
  WITH MOVE Logical_Data_File_Name TO 'C:\some other path\some other name.mdf',
       MOVE Logical_Log__File_Name TO 'C:\some other path\some other name.ldf';