Home / server / Questions / 14827
Accepted
SuperCoolMoss
Asked: 2009-05-30 08:56:21 +0800 CST

Correct procedure for preventing a spid from rolling back

  • 772

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?

sql-server backup-restoration rollback

2 Answers

  1. Best Answer

    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.

    Thanks

    • 3

  2. 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.

    • 0