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.
Reference : Pinal Dave (https://blog.sqlauthority.com)
63 Comments. Leave new
Edwin and Fay, thanks !
I found the easiest solution, when possible, just restart the sql server! This will kill active users and allow you to do whatever you want.
Thanks..
—
THIS WORKED FOR ME
Dave
If you are still getting the error after you try using
use master
go
drop database (databaseName)
go
Close SQL Server Management Studio completely. Open it again and connect as normal. Now you will be able to drop the database with
use master
go
drop database (databaseName)
go
Hi, my database is involved in replication as publisher, I am unable to drop the replication as the database went into suspect mode and I am unable to see the publisher db in local publishers folder.
I want to drop this database but not table to do it, can any one help me
Hi Edwin and Faye,
Your solution works for me. Thanks a lot.
For anyone who has multiple databases on an instance and can’t afford to restart the SQL Server service, here is what I did.
Do an sp_who and make a note of all the spids associated with the DB you want to drop.
Then I created a script to kill the spids and immediatly drop the DB:
kill 58;
go
kill 57;
go
USE Master;
GO
DROP DATABASE AdventureWorks;
GO
excellent…, it worked for me
Hi Edwin Your solution works for me. Thanks
This was driving me crazy since spid 11 from sa in background status was still keeping me from dropping my database, and since I am making small changes and recreating and repopulating it, it was a total waste of time to kept going out and in from SQL Server Management Studio. (Educational environment)
Your blog post was really useful, and I ended up mixing a couple of the solutions given by you all (especially Jim Sz & anand). This one gave results to me, but it still seems to be some kind of timeout that must happen since last USE master command, before this one takes on. But it is much better than going out and in everytime. So here is the script:
ALTER DATABASE dbName
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
USE master
DECLARE @dbid INT
SELECT @dbid = dbid FROM sys.sysdatabases WHERE name = ‘dbName’
IF EXISTS (SELECT spid FROM sys.sysprocesses WHERE dbid = @dbid)
BEGIN
RAISERROR (‘The restore cannot be done’, 16, 1)
SELECT ‘These processes are blocking the restore from occurring’ AS Note, spid, last_batch, status, hostname, loginame FROM sys.sysprocesses WHERE dbid = @dbid
END
DROP DATABASE dbName
Thanks Pinal, this article definitely fixed my problem.
I have always found a solution in your blogs.. THANKS MUCH man.. U are great !! :)
Cheers Bud, this worked a treat.
Thanks, this help me out.
This is the code which worked for me in dropping a test database in Visula studio Test Class [class cleanup()]. Note you will need to add the “Alter database” parameter to the Sqlcommand as well.
SqlConnection sqlConn = new SqlConnection(sqlConnectionString);
sqlConn.Open();
SqlCommand sqlDBCheck = new SqlCommand(“USE Master”, sqlConn);
sqlDBCheck.ExecuteNonQuery();
sqlDBCheck = new SqlCommand(“ALTER DATABASE SM_Test_03 SET SINGLE_USER WITH ROLLBACK IMMEDIATE”, sqlConn);
sqlDBCheck.ExecuteNonQuery();
sqlDBCheck = new SqlCommand(“IF EXISTS(select * from sys.databases where name = ‘SM_Test_03’) DROP DATABASE SM_Test_03”, sqlConn);
sqlDBCheck.ExecuteNonQuery();
sqlConn.Close();
Thanks,
-satish
yester day i dropedn my database some problem but today i want yester day dropped database..pls helpme..any idea pls tell me…
If you have the latest backup, you can restore it to get yesterday’s data
One thing to check is whether the currently logged on user is using the database as it’s default database. You can’t drop the database under this circumstance. Change the default database to master and you should be able to drop it.
This works, Thanks
i had same issue…first step please close all the windows associated with the DB you are trying to delete in the tool you are trying to.
In case u r still getting the error, close ur server, restart it, connect and then run the code below..
USE Master;
GO
DROP DATABASE MobileMetrics;
GO
Is there any problem with this query—
USE MASTER
GO
ALTER DATABASE {DB} SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE {DB}
GO
when i m using this query
CREATE DATABASE distribution ON
( FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\distribution.mdf’)
FOR ATTACH
GO
Database ‘distribution’ already exists. Choose a different database name.