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.

Answer 1

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) 

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