Today I had a situation where I wanted to cancel an update to a table and several indexes and immediately restore back the database from a backup.

I issued a kill command to kill the updating SPID. I then issued a kill with statusonly and there was several tens of thousands of seconds roll back awaiting. I needed to restore the database in a hurry (therefore wasn't concerned with rolling back) - but you cant kill a SPID that's rolling back (as far as I know), or restore a database with outstanding connections.

I decided to stop the sql services, delete the database's mdf and ldf physical files, start the sql services - which shows the database in a suspect state. Then restored the database.

Is there a more controlled / sensible way to approach this situation?


Correct - you cannot kill a SPID that's rolling back, as the database would be put into a transactionally inconsistent state (and possibly even structurally inconsistent).

So the simple answer is that there's no way to prevent a SPID rolling back once it's started, except to do something drastic.

Your situation is a hard one to get out of, as there's really no choice. Even if you bounce SQL Server, the database will go through crash recovery and you'll need to wait until it finishes.

Several possible ways to address this:

  1. Change the update so it happens in smaller batches, so that you don't have the same monster rollback in future (probably much easier said than done)
  2. Restore the database under a different name. Wait for rollback to complete, then drop the original database and rename the restored one.
  3. What you did. The huge risk of this is that your backup is corrupt and then you've lost everything, or some other weird problem occurs and you're in a worse state than before. It also requires that the SQL service is stopped an restarted.

There's basically no good solution once you're in that state except to let the rollback complete. I'm really hesitant to condone what you did because so many people that aren't experienced may try it and get themselves into really deep water.


| improve this answer | |
  • As per my SOP, I'm just upping your answer and moving on. – SQLChicken May 29 '09 at 17:25
  • Thanks Paul, this come about from removing an identity property on a primary key of a large table (didn't realize the implications of the table and non-cluster indexes being recreated - lack of experience on my part) obviously an implicit transaction which couldn't be broken down. After several hours waiting I had to kill the update and restore due to client pressure. Unfortunately there's no space to restore a copy database. VERY happy for you to not condone these actions. I'm beginning to like this website it's like confessional :) – SuperCoolMoss May 29 '09 at 17:55

I think you did it in the most effective way possible, however I'd probably have renamed the files instead of deleting them, just in case. Or move them to another storage location.

| improve this answer | |

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.