Avatar of smt0
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?
Microsoft SQL Server

Avatar of undefined
Last Comment
smt0

8/22/2022 - Mon
assyst

Stop the SQL Server Service and try deleting..it sholud get deleted....
Aneesh

if possible try to stop the server and 'DROP
Aneesh

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
smt0

ASKER
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."
Aneesh

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


smt0

ASKER
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...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Aneesh

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
MacNuttin

Are you connected to the master database when you issue drop database or are you connected to the database you want to drop?
ASKER CERTIFIED SOLUTION
EugeneZ

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
imran_fast

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
pai_prasad

run sp_who2 'active'
then get the spid

and run dbcc inputbuffer (@spid)
to identify the blocking process
EugeneZ

smt0:
Did my solution help you?
smt0

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


Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.