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 != @@SPIDCode 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:
|MoviesDB||57||MAK\mak||Microsoft SQL Server Management Studio||sleeping|
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 57Code 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.