How to delete database, error 5030 database can't be locked

Question

I am trying to delete an existing database in SQL Server 2005. My first attempt produced the following error:

5030: The database could not be exclusively locked to perform the operation.

I have since killed all processes that are accessing the database. I have also removed the replication subscription that it had previously been involved in.

Any thoughts on what else that could be holding the lock on it besides SQL Server processes and replication?

Update: I restarted the server, and that fixed it. I was trying to avoid that, since this is a production server, but hey what can you do?

Answer 1

A production server in which so many connections use the database yet you want to drop it? :)

None the less, how to kick out everybody from the database:

USE [dbname];
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Then drop the database:

USE [master];
DROP DATABASE [dbname];

There is still a very small window of opportunity between the USE [master]; and DROP DATABASE ... where some other connection can grab the 1 single allowed lock on the database, but it usually not worth working around that.

Answer 2

I hate to say it, but a quick solution is to restart the system, make sure the sql server server service is not started, then you should be able to delete.

Also, is IIS stopped if you db is connected to a web ap?

Answer 3

You don't happen to know if anyone left a transaction in an uncompleted rollback state (or otherwise uncompleted)? Might as well check the locks list, too.

Answer 4

In the management studio, goto Management->Activity Monitor (right click) -> View Processes. That will give you a full list of everything running, you can sort the list by Database to see what is still attached, and you can also kill any connections. It's easy to end up with orphaned connections that will prevent you from getting the exclusive access that you need.