Cannot drop database because it is currently in use

by

Problem

When you try to drop the database, you get the following error and the drop fails:

Cannot drop database because it is currently in use.

This means there are other open connections on the database and it won’t let you drop the database.

Disclaimer: The solutions in this article involve kicking out other active connections, which may result in data loss. Use caution.

Solution 1 – Close all other connections and drop the database

You can forcibly close all other open connections by setting the database to single user mode and stopping all active transactions, like this:

USE [master];
GO
ALTER DATABASE [MoviesDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO  
DROP DATABASE [MoviesDB]
Code language: SQL (Structured Query Language) (sql)

The drop database command should now succeed.

Solution 2 – Check for open connections and drop them individually

If you don’t want to forcibly close all connections, and want to verify which connections are open, you can use the following query:

SELECT 
    DB_NAME(dbid) as DBName,
	spid,
    loginame as LoginName,
	program_name,
	status
FROM
    sys.sysprocesses
WHERE 
    DB_NAME(dbid) = 'MoviesDB' AND spid != @@SPID
Code language: SQL (Structured Query Language) (sql)

Note: It’s excluding the connection that is executing this command. You should execute this in the connection you already have open for the DROP DATABASE command.

This outputs the following:

DBNamespidLoginNameprogram_namestatus
MoviesDB57MAK\makMicrosoft SQL Server Management Studiosleeping

In this case, there’s only one other connection. You can close this connection by using the following command and specifying the connection’s spid:

kill 57
Code language: SQL (Structured Query Language) (sql)

Now you can run your DROP DATABASE command.

USE [master];
GO
ALTER DATABASE [MoviesDB] SET SINGLE_USER;
GO  
DROP DATABASE [MoviesDB]
Code language: SQL (Structured Query Language) (sql)

If you’re still getting the “DB in use” error, you may want to just resort to Solution 1 – forcibly closing all active connections.

Related Articles