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)

About these ads

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

  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.

  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

  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

  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

  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

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

  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

  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

  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

  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

  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

  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?

  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

  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.

  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

  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.

  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

  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

  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

  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

  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

  25. yester day i dropedn my database some problem but today i want yester day dropped database..pls helpme..any idea pls tell me…

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

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

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

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

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

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

    DROP DATABASE {DB}
    GO

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

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