How to fix the SQL Server error Cannot drop the database because it is currently in use?
Also know as the 3702 error, the SQL Server error Cannot drop the database because it is currently in use is frequent in multi-user enfironements. 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.
Table of Contents
Guide to fix the SQL Server error Cannot drop the database
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. The exact error message text is the folowwing:
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 :
1. Execute the sp_who2 procedure to identify SPID of active sessions
In the screenshot we identify the active sessions for the DB1 database. We see one user with SPID 51.
2. Close sessions with the SQL Server kill command and SPID
It is the active SPID that prevent the database to be dropped. Indeed, the RDBMS do not allow to drop a database with active sessions.
2.1 Terminate all the sessions with the SPIDs found
Use the Server Process ID (SPID) found in the previous query to kill the session. Execute the query in SSMS.
2.2 Execute again the drop database script without error
Repeat the operation till no active connections are visible on the list. 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.
Conclusion on SQL Server error 3702
This MS SQL tutorial explains how to avoid the common SQL Server error message : Cannot drop the database because it is currently in use. It is a common error and the tip is very useful when creating and dropping multiple databases in development and testing environments for example.
A database, unlike a table, cannot be erased or dumped. A database must be dropped, i.e., completely deleted to remove all its contents. Use the DROP DATABASE command to delete a SQL Server database.
SQL Server administration tutorials
To go further and learn more on database, find more tutorials on the SQL Server database administration topic.