SQL Server – DB is currently in use

Problem

Let’s say you’re running in a test/dev environment and you’re trying to alter the database. For example, you may be trying to drop the database:

DROP DATABASE CSI
Code language: SQL (Structured Query Language) (sql)

But you get the following error message and your command fails:

Cannot drop database “CSI” 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. I wouldn’t recommend doing this in a production environment.

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 [CSI] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO DROP DATABASE [CSI]
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) = 'CSI' 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
CSI57MAK\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 [CSI] SET SINGLE_USER; GO DROP DATABASE [CSI]
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.

Leave a Comment