Cannot drop database because it is currently in use

C#asp.netSql Server

C# Problem Overview


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.

C# Solutions


Solution 1 - C#

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)

Solution 2 - C#

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

https://stackoverflow.com/questions/1711840/how-do-i-specify-close-existing-connections-in-sql-script

I hope it will help you :)

Solution 3 - C#

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

Solution 4 - C#

For SQL server mgmt. studio:

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

Solution 5 - C#

In SQL Server Management Studio 2016, perform the following:

  • Right click on database

  • Click delete

  • Check close existing connections

  • Perform delete operation

Solution 6 - C#

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 !

Solution 7 - C#

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

Solution 8 - C#

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'

Solution 9 - C#

> 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

Solution 10 - C#

A brute force workaround could be:

  1. Stop the SQL Server Service.

  2. Delete the corresponding .mdf and .ldf files.

  3. Start the SQL Server Service.

  4. Connect with SSMS and delete the database.

Solution 11 - C#

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]]]

Solution 12 - C#

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

Solution 13 - C#

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);
        }
    }

Solution 14 - C#

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

Screen

Solution 15 - C#

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.

Solution 16 - C#

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.

Solution 17 - C#

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

Solution 18 - C#

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

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questionsachin kulkarniView Question on Stackoverflow
Solution 1 - C#unruledboyView Answer on Stackoverflow
Solution 2 - C#HiteshView Answer on Stackoverflow
Solution 3 - C#Gregory NozikView Answer on Stackoverflow
Solution 4 - C#AceAlfredView Answer on Stackoverflow
Solution 5 - C#Shashi DharView Answer on Stackoverflow
Solution 6 - C#RazaView Answer on Stackoverflow
Solution 7 - C#LockTarView Answer on Stackoverflow
Solution 8 - C#Abhishek UpadhyayView Answer on Stackoverflow
Solution 9 - C#Sajid khanView Answer on Stackoverflow
Solution 10 - C#HerbalMartView Answer on Stackoverflow
Solution 11 - C#Don RollingView Answer on Stackoverflow
Solution 12 - C#Luke VoView Answer on Stackoverflow
Solution 13 - C#ChickenView Answer on Stackoverflow
Solution 14 - C#Alper EbicogluView Answer on Stackoverflow
Solution 15 - C#KV PrajapatiView Answer on Stackoverflow
Solution 16 - C#AceMarkView Answer on Stackoverflow
Solution 17 - C#user5509972View Answer on Stackoverflow
Solution 18 - C#Francesco MantovaniView Answer on Stackoverflow