Cannot drop database because it is currently in use

Question

I want to drop a database. I have used the following code, but to no avail.

public void DropDataBase(string DBName,SqlConnection scon)
{
    try
    {
        SqlConnection.ClearAllPools();
        SqlCommand cmd = new SqlCommand("ALTER DATABASE " + DBName + "     SET SINGLE_USER     WITH ROLLBACK IMMEDIATE", scon);
        cmd.CommandType = CommandType.Text;
        scon.Open();
        cmd.ExecuteNonQuery();
        scon.Close();

        SqlCommand cmddrpdb = new SqlCommand("drop database " + DBName + "", scon);
        cmddrpdb.CommandType = CommandType.Text;
        scon.Open();
        cmddrpdb.ExecuteNonQuery();
        scon.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show("DropDataBase : " +ex.Message);
    }
}

I am getting Error as cannot drop database because it is currently in use. Please help me out in the above mentioned issue.

score 162 · Answer 1 · edited Mar 04 '22 at 17:06

162

Before dropping a database, you will need to drop all the connections to the target database first.

I have found a solution at http://www.kodyaz.com/articles/kill-all-processes-of-a-database.aspx

DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'YOUR_DABASE_NAME'

DECLARE @SQL varchar(max)

SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

--SELECT @SQL 
EXEC(@SQL)

edited Mar 04 '22 at 17:06

José Luis Marrero

17
5

answered Sep 19 '11 at 09:58

unruledboy

2,455
2
23
30

3

+1 I wonder why you did not get this earlier, this is a very handy answer. Thanks – Mehdi LAMRANI Mar 08 '12 at 11:18
Won't that bring in non-user processes as well? – monksy Oct 08 '13 at 22:27
For me I get the error "KILL command cannot be used inside user transactions." – Ally May 09 '14 at 13:59
If the transaction is causing a problem, don't use the transaction. – Don Rolling Dec 08 '15 at 16:50
1

FYI, this seems to do what you said it would, but isn't solving the issue for me. – Don Rolling Dec 08 '15 at 16:51
You should wrap the Exec() call in a conditional: IF (@SQL IS NOT NULL) OR (LEN(@SQL) > 0). Otherwise it will fail in the situations where there are no connections. – suneg Apr 28 '17 at 07:21

Hitesh · Answer 2 · 2021-06-17T16:16:57.870

139

It's too late, but it may be useful for future users.

You can use the below query before dropping the database query:

 use master go
 alter database [MyDatbase] set single_user with rollback immediate

 drop database [MyDatabase]

It will work. You can also refer to

How do I specify "close existing connections" in sql script

I hope it will help you :)

edited Jun 17 '21 at 16:16

answered Dec 13 '13 at 14:42

Hitesh

3,508
1
18
24

2

don't forget to add this at the begining ```use master go ``` as well if you currently select [MyDatabase] – Thành Chung Bùi Jun 20 '19 at 06:29
1

@ThànhChungBùi what you pointed at solved my problem. It should be included in the answers. Thank you. – Shahryar Saljoughi Sep 08 '20 at 13:14

score 83 · Accepted Answer · edited Jul 31 '13 at 12:58

83

Someone connected to the database. Try to switch to another database and then, to drop it:

Try

SP_WHO to see who connected

and KILL if needed

edited Jul 31 '13 at 12:58

Brian J

694
1
21
34

answered Sep 19 '11 at 09:58

Gregory Nozik

3,296
3
32
47

27

For SQL server mgmt. studio: Right click database: Properties -> Options -> Restrict Access : Set to "Single User" and perform the drop afterwards. – AceAlfred Sep 27 '13 at 11:02
invoke-sqlcmd -ServerInstance localhost 'exec sp_who' | where-object {$_.dbname -eq 'myDbName'} returns nothing. Yet it till complains. This doesn't seem to actually work. – Pxtl Mar 12 '20 at 21:56
1

@AceAlfred Yours is the easiest one among all. Should have been one of the answers. Thanks. – Amit Ray Nov 27 '20 at 06:08

score 58 · Answer 4 · answered Sep 27 '13 at 11:03

58

For SQL server mgmt. studio:

Right click database: Properties -> Options -> Restrict Access : Set to "Single User" and perform the drop afterwards

answered Sep 27 '13 at 11:03

AceAlfred

1,111
3
21
35

1

If you want to delete a database right from the Management Studio, this is the most intuitive solution. Thank you. – Andre Pena Jan 14 '16 at 18:05
2

I did it, and then used `drop database myDatabase`. It didn't solve the problem. the same error (`... it is currently is use`) happned. – Shahryar Saljoughi Sep 08 '20 at 13:11
@ShahryarSaljoughi I just followed acealfred 's instructions. Perhaps the menu approach executes some other commands beyond a simple drop. – Michael May 24 '21 at 22:28

score 47 · Answer 5 · edited Oct 31 '16 at 18:21

47

In SQL Server Management Studio 2016, perform the following:

Right click on database
Click delete
Check close existing connections
Perform delete operation

edited Oct 31 '16 at 18:21

DForck42

19,789
13
59
84

answered Oct 31 '16 at 16:29

Shashi Dhar

603
6
7

score 35 · Answer 6 · answered May 12 '14 at 07:33

select * from sys.sysprocesses where dbid = DB_ID('Test')

(Replace 'Test' with the name of the database you are trying to drop) This will tell you which processes are using it.

If you still want to force drop then, the ultimate approach is:

USE master;
GO
ALTER DATABASE Test 
SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE Test;

Hope this helps !

score 9 · Answer 7 · answered Jun 03 '17 at 10:25

9

First make your data base offline after that detach it e.g.

Use Master
GO
ALTER DATABASE dbname SET OFFLINE
GO
EXEC sp_detach_db 'dbname', 'true'

answered Jun 03 '17 at 10:25

Abhishek Upadhyay

208
3
9

1

I dont think action deletes the database. – snp.it Mar 11 '19 at 17:37
2

after detaching the database now it ready to be deleted with this query. `DROP DATABASE dbname; ` – Hidayat Arghandabi Oct 14 '19 at 07:51

score 8 · Answer 8 · answered Apr 03 '14 at 05:43

8

If your dropping the database in SQL Management Studio and you get the message, don't forget that you use Master as selected database otherwise your query is also an connection to the database.

USE Master;
GO
DROP DATABASE AdventureWorks;
GO

answered Apr 03 '14 at 05:43

LockTar

5,364
3
46
72

I my case I had more than 1 connection to the DB I I wanted to drop. – AlexMelw Jun 15 '17 at 05:10

score 8 · Answer 9 · answered Aug 29 '20 at 06:40

First check the connected databases

SP_WHO

Second Disconnect your database

DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'your_database_name'

DECLARE @SQL varchar(max)

SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

--SELECT @SQL 
EXEC(@SQL)

FINALLY DROP IT

drop database your_database

score 3 · Answer 10 · answered Apr 08 '13 at 09:19

3

A brute force workaround could be:

Stop the SQL Server Service.
Delete the corresponding .mdf and .ldf files.
Start the SQL Server Service.
Connect with SSMS and delete the database.

answered Apr 08 '13 at 09:19

HerbalMart

1,669
3
27
50

It works for me. Thanks. I think the problem is an external hard drive. I switched the file to a local disk. Thanks for your solution. – InfZero Nov 02 '14 at 21:16

score 3 · Answer 11 · answered Jun 22 '20 at 15:30

3

To delete a database even if it's running, you can use this batch file

@echo off

set /p dbName= "Enter your database name to drop: " 

echo Setting to single-user mode
sqlcmd -Q "ALTER DATABASE [%dbName%] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE"

echo Dropping...
sqlcmd -Q "drop database %dbName%"

echo Completed.

pause

answered Jun 22 '20 at 15:30

Alper Ebicoglu

8,884
1
49
55

1

Idea with single user mode seems to be the cleanest way to do it. – Gucu112 Jan 04 '21 at 11:35

score 2 · Answer 12 · answered Dec 08 '15 at 17:06

I wanted to call out that I used a script that is derived from two of the answers below.

Props to @Hitesh Mistry and @unruledboy

DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'[[[DatabaseName]]]'

DECLARE @SQL varchar(max)

SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

EXEC(@SQL)

alter database [[[DatabaseName]]] set single_user with rollback immediate

DROP DATABASE [[[DatabaseName]]]

score 1 · Answer 13 · answered Jan 08 '14 at 14:37

1

Using MS SQL Server 2008, in DELETE dialog with Close connection options, this is the generated script, I guess it is the best:

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'YOUR_DATABASE_NAME'
GO
USE [master]
GO
ALTER DATABASE [YOUR_DATABASE_NAME] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
/****** Object:  Database [YOUR_DATABASE_NAME]    Script Date: 01/08/2014 21:36:29 ******/
DROP DATABASE [YOUR_DATABASE_NAME]
GO

answered Jan 08 '14 at 14:37

Luke Vo

17,859
21
105
181

This is what SQL Server Management Studio scripts out and what happens to work for me. The only thing I did differently was wrap all of that inside of a `USE [master] IF EXISTS(SELECT * FROM sys.databases WHERE name = 'Database_Name') BEGIN -- your script here END` – Smitty-Werben-Jager-Manjenson Aug 19 '20 at 23:19

score 1 · Answer 14 · edited Aug 26 '15 at 16:22

Just wanted to give a vb.net (as with c language if want to convert..) I was having similar problem for uninstal of one of my programs, dropping the DB was bit tricky, yes could get users to go into server drop it using Express, but thats not clean, after few looks around got a perfect little bit of code together...

    Sub DropMyDatabase()
    Dim Your_DB_To_Drop_Name As String = "YourDB"
    Dim Your_Connection_String_Here As String = "SERVER=MyServer;Integrated Security=True"
    Dim Conn As SqlConnection = New SqlConnection(Your_Connection_String_Here)

    Dim AlterStr As String = "ALTER DATABASE " & Your_DB_To_Drop_Name & " SET OFFLINE WITH ROLLBACK IMMEDIATE"
    Dim AlterCmd = New SqlCommand(AlterStr, Conn)

    Dim DropStr As String = "DROP DATABASE " & Your_DB_To_Drop_Name
    Dim DropCmd = New SqlCommand(DropStr, Conn)

    Try
        Conn.Open()
        AlterCmd.ExecuteNonQuery()
        DropCmd.ExecuteNonQuery()
        Conn.Close()

    Catch ex As Exception
        If (Conn.State = ConnectionState.Open) Then
            Conn.Close()
        End If
        MsgBox("Failed... Sorry!" & vbCrLf & vbCrLf & ex.Message)
    End Try
End Sub

Hope this helps anyone looking xChickenx

UPDATE Using this converter here is the C# version :

public void DropMyDatabase()
    {
        var Your_DB_To_Drop_Name = "YourDB";
        var Your_Connection_String_Here = "SERVER=MyServer;Integrated Security=True";
        var Conn = new SqlConnection(Your_Connection_String_Here);

        var AlterStr = "ALTER DATABASE " + Your_DB_To_Drop_Name + " SET OFFLINE WITH ROLLBACK IMMEDIATE";
        var AlterCmd = new SqlCommand(AlterStr, Conn);

        var DropStr = "DROP DATABASE " + Your_DB_To_Drop_Name;
        var DropCmd = new SqlCommand(DropStr, Conn);

        try
        {
            Conn.Open();
            AlterCmd.ExecuteNonQuery();
            DropCmd.ExecuteNonQuery();
            Conn.Close();

        }
        catch(Exception ex)
        {
            if((Conn.State == ConnectionState.Open))
            {
                Conn.Close();
            }
            Trace.WriteLine("Failed... Sorry!" + Environment.NewLine + ex.Message);
        }
    }

@MathiasLykkegaardLorenzen there are several freeware apps that can translate .NET code across all languages. There even online apps. — Ognyan Dimitrov, Aug 25 '15 at 13:45
Point taken. I would have changed my vote, but I can't, as it is locked. — Mathias Lykkegaard Lorenzen, Aug 25 '15 at 17:07
**BAD IDEA!** Dropping an database when it is offine will not remove the database files! Even if this might be handy in some special case, it's not a pattern you should blindly follow. Problems: a) your disk gets filled and b) you might run into issues when trying to recreate the database using the same physical files. — Stefan Steinegger, Feb 21 '17 at 08:40
@OgnyanDimitrov One more reason to provide a C# answer. Otherwise why not post a python, powershell, c++, assembly ways of doing it? Or post it as base64 text for those who would want an answer written in base64? I am not downvoting or anything, but MathiasLykkegaardLorenzen point is valid. — P-L, Aug 28 '23 at 13:53

score 1 · Answer 15 · answered Apr 07 '23 at 00:20

1

All I did was switched the database from the top in SSMS to Master and then wrote drop database with the name of my DB and it worked perfectly. The issue was that I had the DB set to the same name as the DB I wanted to drop, thats why it was currently in use!

answered Apr 07 '23 at 00:20

jon Thomas

11
1

This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/34178442) – Standin.Wolf Apr 10 '23 at 20:49

score 1 · Answer 16 · edited Apr 17 '23 at 17:44

1

First you should Change Data Base State To Single User
Then go to master Data Base
And Finally Drop Data Base

For Example

My Data Base Name Is Test1

ALTER DATABASE Test1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

USE master
GO
DROP DATABASE Test1

edited Apr 17 '23 at 17:44

Trenton McKinney

56,955
33
144
158

answered Apr 17 '23 at 13:21

امید

11
2

score 0 · Answer 17 · answered Dec 13 '13 at 14:37

0

just renaming the DB (to be delete) did the trick for me. it got off the hold of whatever process was accessing the database, and so I was able to drop the database.

answered Dec 13 '13 at 14:37

AceMark

701
1
11
21

score 0 · Answer 18 · answered Jul 18 '16 at 10:51

0

Go to available databases section and select master. Then Try DROP DATABASE the_DB_name.

answered Jul 18 '16 at 10:51

score 0 · Answer 19 · answered Jun 08 '21 at 08:03

Use this:

/* Delete Database Backup and Restore History from MSDB System Database */
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'[dba]'
GO

/* Query to Get Exclusive Access of SQL Server Database before Dropping the Database  */
USE [master]
GO

ALTER DATABASE [dba]

SET SINGLE_USER
WITH

ROLLBACK IMMEDIATE
GO

/* Query to Drop Database in SQL Server  */
DROP DATABASE [dba]
GO

score 0 · Answer 20 · answered Sep 19 '11 at 10:02

0

You cannot drop a database currently being used however you can use sp_detach_db stored procedure if you want to remove a database from the server without deleting the database files.

answered Sep 19 '11 at 10:02

KV Prajapati

93,659
19
148
186

score 0 · Answer 21 · answered Mar 14 '23 at 10:28

0

When you drop the DB from the SSMS UI, it also kills all connections to this DB.

answered Mar 14 '23 at 10:28

FFFffff

776
7
18

Cannot drop database because it is currently in use

21 Answers21

Linked

Related