troubleshooting Question

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

Avatar of smt0
smt0 asked on
Microsoft SQL Server
13 Comments1 Solution10159 ViewsLast Modified:
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?
ASKER CERTIFIED SOLUTION
EugeneZ
SQL SERVER EXPERT

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros