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?
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):
If you know what operation is rolling back, you may also be able to infer some guesses on progress by looking at the columns
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] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [databasename] ;
Alternatively, you can check the status of rollback using
kill SPID with statusonly