Cannot drop the database because it is currently in use

How to avoid the SQL error Cannot drop the database because it is currently in use ? It is possible to manage SQL databases with scripts or via the various windows of SQL Server Management Studio. For example, you can simply delete a database with a SQL DROP DATABASE command. However, if there are active connections on the database being deleted, then the database returns an error message, because a database with an active connection cannot be deleted.

How to handle the error message “Cannot drop the database because it is currently in use. Microsoft SQL Server Error 3702”

The exact error message text is this one: “Cannot drop database “DB1″ because it is currently in use. (Microsoft SQL Server, Error: 3702)”.

This article is about how to create a database with default options with the SSMS graphic user interface.

Note: Be careful to check the database backups before deleting it completely. Especially in important projects and production environments.

This type of script generates the error:

USE [master];
GO

DROP DATABASE [DB1];
GO

Another way is to check the database existence on the server, before running the Drop Database command, like this for example:

USE [master];
GO

IF EXISTS (
	SELECT name 
	FROM master.dbo.sysdatabases
	WHERE name = 'DB1'
)
DROP DATABASE [DB1];
GO

To avoid this error, close all active connections to the database before the drop and terminate the current queries. Then close the tabs in SSMS or explicitly end the open connections on the database. Finally close the active tabs if only one user is currently connected.

For the second step, run these two operations :

First operation: execute the sp_who2 command to identify the active sessions

In the screenshot we try to delete the DB1 database:

sql server command to check the active connection to drop the database

Second operation: close them with the SQL Server kill command followed by the SPID identified:

Repeat the operation till no active connections are visible on the list.

Now the database is dropped with no error message.

To go further, here is the official documentation of the T-SQL kill command. The “unable to drop the database because it is currently in use” sql command error is a classical one. Indeed it’s an object that allows many connections from different users at the same time.