3

I want to drop a SQL Server 2008 database, but it is currently performing a rollback for a transaction (that transaction took 3 days to run so far).

How do I stop the rollback because I want to drop the database?

Thank you.

Improve this question
1
  • 3
    You will need to live with the database until the rollback is complete. Just have patience and let it do its job.
    – Aaron Bertrand
    Commented Oct 10, 2014 at 13:36

3 Answers 3

Reset to default
2

Here's how I stopped the rollback and dropped the database.

  1. Stop MSSQL Server via its service applet.
  2. Delete the corresponding database files (the mdf and log file) from Windows Explorer.
  3. Restart SQL server via its service applet.
  4. Start SQL Management Studio, go to the corresponding database, right click and select Delete.
Improve this answer
1

Rollbacks will generally take as long to roll back as they took to run.

If you are only dropping the database you restart the engine which would allow you to then drop the database, as this would kill the rollback process. Obviously make sure you have downtime ;)

Improve this answer
5
  • 4
    When an instance restarts SQL Server will try to bring each DB back online. To do this it must complete any outstanding transaction i.e. finish the rollback. You will be no further ahead by restarting the instance.
    – Michael Green
    Commented Oct 10, 2014 at 10:23
  • 2
    They will often take longer, as rollbacks are single-threaded (so consider a parallel index create, for example).
    – Aaron Bertrand
    Commented Oct 10, 2014 at 13:36
  • 4
    If the intent is to drop the database, delete the database files before restarting the instance. Drop the suspect database after the restart.
    – Dan Guzman
    Commented Oct 10, 2014 at 16:17
  • If this is a dev instance and you feel adventurous you can use Process Explorer to close all file handles to that database. This might cause corruption inside SQL Server's process. Hopefully it gives up on that database quickly.
    – usr
    Commented Oct 12, 2014 at 18:34
  • I did something like that once and it turned out to be a Really Bad Idea. I'd rather just restart the instance than stomp on file handles and get potentially undefined behavior.
    – James Lupolt
    Commented Oct 12, 2014 at 22:50
1

In short you can't. You have to wait for the transaction to rollback before the locks are released.

I suppose you could stop the SQL service and delete the database files. Then when the instance comes back online delete the suspect database. But this requires an outage and that you are very sure you are deleting the correct files.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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