Cannot drop the database because it is currently in use in Sql Server

How to avoid the SQL error Cannot drop the database because it is currently in use ? Also know as the 3702 SQL Server error. 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.

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

Fix the SQL Server 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 because it is currently in use. (Microsoft SQL Server, Error: 3702)”.

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 remaining active sessions.

In the screenshot we identify the active sessions for the DB1 database. We see one user with SPID 51.

sp_who2

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

Use the SPID found in the previous query.

kill 51

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.

This article explained how to avoid the common SQL Server error message : Cannot drop the database because it is currently in use.

To go further and learn more on database, here is how to install a second SQL Server instance on a server.