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.
-
3You will need to live with the database until the rollback is complete. Just have patience and let it do its job.– Aaron BertrandCommented Oct 10, 2014 at 13:36
Add a comment
|
3 Answers
Here's how I stopped the rollback and dropped the database.
- Stop MSSQL Server via its service applet.
- Delete the corresponding database files (the mdf and log file) from Windows Explorer.
- Restart SQL server via its service applet.
- Start SQL Management Studio, go to the corresponding database, right click and select Delete.
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 ;)
-
4When 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. Commented Oct 10, 2014 at 10:23
-
2They will often take longer, as rollbacks are single-threaded (so consider a parallel index create, for example). Commented Oct 10, 2014 at 13:36
-
4If the intent is to drop the database, delete the database files before restarting the instance. Drop the suspect database after the restart. 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.– usrCommented 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. Commented Oct 12, 2014 at 22:50
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.
