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 (http://blog.SQLAuthority.com)
Hi,
I have a question.
If the statements are executed as a batch the above solution works but if i am to execute each SQL statement from a test tool , what would be a solution?
Thanks in advance,
Leena
I did same way
USE Master;
GO
DROP DATABASE Test;
GO
but still I am getting
Msg 3702, Level 16, State 4, Line 1
Cannot drop database “Test” because it is currently in use.
Hi,
well in case if you get the same error even whne use the SQL master wht whould be the couse and the salution for it
thank you
regards
rebecca
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
Yes, this tip worked. Great. Thanks.
5 years later still working fine^^ thx
Pingback: SQL SERVER - FIX : Error : 3702 Cannot drop database because it is currently in use - Part 2 Journey to SQL Authority with Pinal Dave
Here’s a little script I have to determine who is using the database in use.
USE Master
DECLARE @dbid INT
SELECT @dbid = dbid FROM sys.sysdatabases WHERE name = ”
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
Pingback: SQL SERVER - 2005 - Retrieve Processes Using Specified Database Journey to SQL Authority with Pinal Dave
Dear every body
i think your are talking in case you are the only one who are using the database what about case that database used by a lot of users
do SqlConnection.ClearAllPools();from code
(using System.Data.SqlClient)
to clean process instance on SqlServer before drop database
Hi there , I also passed the same query i.e
USE AdventureWorks;
GO
DROP DATABASE AdventureWorks;
GO
But its still giving me the same error.
Thanks
Gagan
run sp_who to find out which user is using the db
Hi Try this command and it will allow u to delete ur database
ALTER DATABASE db_name
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
Hey Guys,
Thanks for tip, it does work for me to alter to single user than drop.
Thanks,
Jay
Thank u very much it is working
Hi,
I found this blog while searching for a work around for sql databases that are stuck in restoring mode and cannot be dropped because they are in use by another process. I then ran the following SQL against the master database and to my surprise the database finished restoring and is completely usable. I have no idea how this works, but did it truly work and am I at risk using this work around?
USE Master
DECLARE @dbid INT
SELECT @dbid = dbid FROM sys.sysdatabases WHERE name = ‘AirCl2Loss_GuideOne_20080630_TSv10_ScenB’
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
I got this error in my C# console app (even after using SqlConnection.ClearAllPools(), as suggested) because I was trying to access a SQL table and fill it into a DataTable when it was already dropped by another function. I figured out I needed a try…catch around that block of code. Someone else might benefit from that little nugget of knowledge….
-Tom
I had a place in my program where I was trying to drop the database I had already dropped some tables from and was attempting to use the same database connection to drop the database. Turns out this doesn’t work and gives me the 3702 error. So I put
try
{
// DB Connection String
// Table dropping
// Close DB Connection
}
catch
{
// Exception handling
}
and
try
{
// DB Connection String (Initial Catalog=master)
SqlCommand sqlDBCheck = new SqlCommand(“USE Master”, sqlConn);
sqlDBCheck.ExecuteNonQuery();
sqlDBCheck = new SqlCommand(“IF EXISTS(select * from sys.databases where name = ‘TestDB’) DROP DATABASE TestDB”, sqlConn);
sqlDBCheck.ExecuteNonQuery();
// Close DB Connection
}
catch
{
// Exception handling
}
Two separate try…catches with two separate connection strings. Worked for me, hope it helps someone else out there.
-Tom
I have just read this entire article but nothing applies to my issue.
I have setup a bog standard SSIS package using the wizard to copy a database at a given period.
No matter what I do I keep getting that blasted error message… Can not drop database “DAtabase_Name” because it is currently in use.
I have used the sp_who and there is no reference to the database in question… I have ensured the permissions of the user running the job are all good… And to top it off.. I can login in to SSMS using the same login and run the DROP DATABASE query… Which works… But the job just won’t work… Any ideas?
Same thing with me. I have the same issue. Nothing here works.
1. Stop the database engine via the “SQL Server Surface Area Configuration”
2. delete the .mdf and the .ldf file in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL
3. Start the service in the “SQL Server Surface Area Configuration”
4. Delete the database in the “SQL Server management Studio”
Worked for me.. I had databases that where “In recovery” way to long for my liking.
I had the .mdf and .ldf files backed up somewhere else so after this made a new database and the restored the data from these backups and voila.
Greetings Edwin Hebbink
Edwin,
The steps that you provide is working for me – thanks.
Edwin,
Thanks for your solutions, it works for me, however with a little different approach. I tried to stop the database engine via the “SQL Server Surface Area Configuration”, but it didn’t stop the database engine successfully. So I tried to stop the “SQL Server Agent(MSSQLSERVER)” and “SQL Server (MSSQLSERVER)” via the Services and it works. The rest of the steps works for me, except that I rename the .mdf and .ldf file instead of deleting it.
Edwin,
Your solution worked for me. Thank you.
Edwin:
thank you so much, this works for me too.
I used the solution provided by Jim Sz, but I’m still puzzled:
I used the above snippet to figure out why I was unable to drop my table.. looks like the ’sa’ account is using it in background….
(status = background and loginame = sa)
I’m using SQL Server Express 2008, in Windows Authentication mode, and I’m the only person using the database at the moment.
So I’m not sure where ’sa’ is coming from… or how to stop it from using my database when I need to drop it
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
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
Pingback: SQL SERVER – Weekly Series – Memory Lane – #006 « SQL Server Journey with SQL Authority
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.