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
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
this worked
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
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
this will also not works
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 FilesMicrosoft SQL ServerMSSQL.1MSSQL
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