Link to home
Start Free TrialLog in
Avatar of smt0

asked on

Cannot drop the database because it is currently in use. Cannot kill process.

I have a SQL 2000 server and a database that I want to drop.

A drop command returns "Cannot drop the database 'MyDB' becuase it is currently in use.

I tracked down the process stopping the drop and tried to Kill it, but this had no effect. I am fairly sure this is a transaction that got left open when an application crashed out as it is quite old and the PC involved has been re-booted several times since then.

So I tried 'ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE' to try and force the issue, but this command runs but never completes.

How can I clear this open transaction and/or drop the database without having to re-boot the server?
Avatar of assyst

Stop the SQL Server Service and try sholud get deleted....
Avatar of Aneesh
if possible try to stop the server and 'DROP
otherwise use sp_who and find the processIds which are using that particular db, kill all the connections , and try to drop the DB from the 'Master' db
Avatar of smt0


I want to do this without stopping the SQL Server Service if possible as this is a production server.

As I said in my post, I have tried issuing the KILL command to the relevant SPID but this fails and returns the message "SPID 109: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds."
In order to maintain transactional consistancy, SQL must undo any work done in a transaction when a process is killed.  This is called Rollback and typically takes as long as the original transaction. This wil be alright Soon. You should wait till it rollbacks

Avatar of smt0


The original transaction that is causing this issue was a CREATE VIEW statement and so I wonder why this should take such a long time to roll-back? I have been issuing kill statements to this process for about an hour now and no luck, so I am not confident that waiting will bring a result. However, in the absence of any alternative, I'll wait some more...
Does that involve distributed Queries  or the database is used in replication ? in these senarios i found the rollback status was going upto 4 hrs
Are you connected to the master database when you issue drop database or are you connected to the database you want to drop?
Avatar of EugeneZ
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
hi smt0 ,

Right Click your Database in Enterprise Manager
All Tasks
Detach Database
Click on Clear Button (will kill all existing connections)
Click on Cancel
Drop Database
run sp_who2 'active'
then get the spid

and run dbcc inputbuffer (@spid)
to identify the blocking process
Did my solution help you?
Avatar of smt0


Yes thank you,

The SPID was related to view on a linked server, only problem was , we had already dropped the other table on the linked server.
so we could not kill it. In the end we stopped and restarted overnight.