0

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

  • If you dont care about current db, rename this to old and restore the db you wanted. This should work or taking it offline if the spid is least to be cared for.. this is to avoid sql restart. – BeginnerDBA Nov 9 '18 at 0:01
  • I couldn't rename the db data file as it was in use. I read elsewhere that restarting the server means the rolling back transaction will take even longer to roll back after restart. Regardless, I restarted the server then found no SPIDS in rollback state. This appears to have freed up the DB but I don't understand what would have happened to the transactional consistency of that rolling back SPID. Am now restoring from backup. – youcantryreachingme Nov 9 '18 at 0:09
2

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';
|improve this answer|||||
  • Let it rollback when it's estimating that will take 3 years? Somehow I've recovered it, but that did require a restart of the server (after which there were no SPIDS in rollback state any longer). – youcantryreachingme Nov 9 '18 at 0:38
  • 3
    That's great that that worked this time, but it won't always. Forcing a restart could put you back at the start of that rollback process, or worse. What if that spid also had locks in another database, one that you do care about, and now you put that database in jeopardy too? That other database could be inaccessible for as long or longer, or it could have gone into suspect or become corrupt. Ctrl + Alt +Del or pulling the power cord works great, until it doesn't, and there is no undo. – Aaron Bertrand Nov 9 '18 at 0:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.