SQL Server DROP Database which is in a long roll back process

Posted on

Question :

I have a DataBase that is taking very long to rollback a transactions.
Which could be normally because it is rolling back a hugh amount of data.

However I have the scripts to create the DB again and just want to drop the DB so that this roll back stops…

How can I achieve this?

thanks already!

kind regards

Answer :

You’re just going to need to wait, unless you want to risk corrupting other databases by, say, yanking the power cord out of the wall. You can set your other user databases offline before attempting such a stunt, but good luck with system databases.

For a rollback you should be able to guesstimate how much longer you’ll need to wait by running this query:

  [est_finish] = DATEADD(MILLISECOND,[estimated_completion_time],CURRENT_TIMESTAMP)
FROM sys.dm_exec_requests

I blogged about this here (and it includes a much more exhaustive query):

If you know what operation is rolling back, you may also be able to infer some guesses on progress by looking at the columns reads,writes and logical_reads – but don’t spend too much time on that, since I/O is what percent_complete is based on, so you’ll probably come to roughly the same conclusion.

Still, this is just information that will help you set expectations about how long you need to wait. There isn’t a very safe way to cancel a rollback.

You can use sp_whoisactive to find out what is holding lock on the database or what SPID is rolling back.

You can issue :

ALTER DATABASE [databasename] 
DROP DATABASE [databasename] ;

Alternatively, you can check the status of rollback using kill SPID with statusonly

Leave a Reply

Your email address will not be published. Required fields are marked *