SQL SERVER – FIX : Error : 3702 Cannot drop database because it is currently in use.

Msg 3702, Level 16, State 3, Line 2
Cannot drop database “DataBaseName” because it is currently in use.

This is a very generic error when DROP Database is command is executed and the database is not dropped. The common mistake user is kept the connection open with this database and trying to drop the database.

The following commands will raise above error:

USE AdventureWorks;
GO
DROP DATABASE AdventureWorks;
GO



Fix/Workaround/Solution:
The following commands will not raise an error and successfully drop the database:

USE Master;
GO
DROP DATABASE AdventureWorks;
GO

If you want to drop the database use master database first and then drop the database.

When You Still Cannot Drop Database After Switching to Master

Switching to master fixes the most common case, which is your own connection. If the error still appears, some other session is using the database. It can be another query window, an application, a SQL Agent job or Object Explorer in Management Studio. On SQL Server 2005 you can find them with SELECT spid, loginame, hostname, program_name FROM sys.sysprocesses WHERE dbid = DB_ID('DataBaseName'), or with sp_who2.

Once you know who is connected, you have two choices. You can ask them to disconnect, or you can force them out. To force them out, run ALTER DATABASE DataBaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE in the same batch as the DROP DATABASE statement. ROLLBACK IMMEDIATE rolls back any open transactions and disconnects every other user right away. The Delete dialog in Management Studio has a similar option called Close existing connections.

Please be careful with this. Forcing users out on a shared server can cancel someone’s long running work, and dropping a database cannot be undone. Before I drop anything that is not a scratch copy, I follow three simple rules:

• Take a full backup and confirm the backup file exists.
• Double check the server name and the database name in the query window.
• Tell the people who use it, even if it is only a test database.

If you drop and restore the same test copy often, keep a small saved script that does every step in the right order: switch to master, set single user with rollback, drop, then restore. A saved script is much safer than typing it from memory each time.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.