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

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 1

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:

SELECT 
  percent_complete, 
  [est_finish] = DATEADD(MILLISECOND,[estimated_completion_time],CURRENT_TIMESTAMP)
FROM sys.dm_exec_requests
WHERE command LIKE N'%ROLLBACK'; -- could be KILLED/ROLLBACK

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

• When will my backup / restore / index reorganize finish?

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.

Answer 2

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] 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [databasename] ;

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