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.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

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

  1. 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

    Like

  2. 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.

    Like

  3. 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

    Like

  4. 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

    Like

  5. Pingback: SQL SERVER - FIX : Error : 3702 Cannot drop database because it is currently in use - Part 2 Journey to SQL Authority with Pinal Dave

  6. 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

    Like

  7. Pingback: SQL SERVER - 2005 - Retrieve Processes Using Specified Database Journey to SQL Authority with Pinal Dave

  8. 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

    Like

  9. do SqlConnection.ClearAllPools();from code
    (using System.Data.SqlClient)
    to clean process instance on SqlServer before drop database

    Like

  10. 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

    Like

  11. 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

    Like

  12. 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

    Like

  13. 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

    Like

  14. 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

    Like

  15. 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?

    Like

  16. 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

    Like

  17. 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.

    Like

  18. 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

    Like

  19. 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.

    Like

  20. 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

    Like

  21. 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

    Like

  22. 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

    Like

  23. 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

    Like

  24. 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

    Like

  25. 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.

    Like

  26. Pingback: SQL SERVER – Weekly Series – Memory Lane – #006 « SQL Server Journey with SQL Authority

  27. 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

    Like

  28. Is there any problem with this query—
    USE MASTER
    GO

    ALTER DATABASE {DB} SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    GO

    DROP DATABASE {DB}
    GO

    Like

  29. 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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s