5

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

0
3

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 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.

4
  • If it's an option, I believe you can stop the SQL service, rename the .mdf file and start the service. The database would show up as Recovery Pending and you can delete it then. – FilamentUnities Jan 8 '15 at 15:55
  • @ZabadakGalorex Forcefully stopping the SQL Service, kind of like unplugging the power cord? Again, that is not exactly a "safe" thing to try. – Aaron Bertrand Jan 8 '15 at 15:58
  • Would a regular stop of the service wait for the rollback to complete? Or would it require a shutdown with nowait? – FilamentUnities Jan 8 '15 at 16:04
  • 2
    Pretty sure it would wait because it would encounter the same rollback operation when it tried to safely take that database offline. – Aaron Bertrand Jan 8 '15 at 16:07
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

5
  • Altering of the Database does not work because it cannot be locked. – OverflowStack Jan 8 '15 at 10:19
  • 1
    my point was to find out what SPID is locking the database exclusively. Kill that SPID, so you can drop the database. – Kin Shah Jan 8 '15 at 10:47
  • 2
    you can not kill a SPID that is doing a rollback.. :( for perfect understandable reasons that this would make your DB inconsistent. However in this case I dont care and want to even fully drop my DB! :-) – OverflowStack Jan 8 '15 at 10:50
  • 1
    you can not kill a SPID that is doing a rollback .. Thats why I mentioned that you can check the status of rollback using kill spid with statusonly. You have to wait for the rollback to finish. – Kin Shah Jan 8 '15 at 11:04
  • OK thank you :-) I hoped that there is a way around waiting. As it is currently still rolling back.. – OverflowStack Jan 8 '15 at 11:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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