Cannot drop database because it is currently in use

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.

Solution 1:

before dropping a database, you drop the connection to that 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:

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 :)

Solution 3:

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:

For SQL server mgmt. studio:

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