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

User · Answer

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

User · Answer

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  lt  gt    SPId  --SELECT  SQL  EXEC  SQL    FINALLY DROP IT  drop database your database

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt  gt    SPId  --SELECT  SQL  EXEC  SQL

User · Answer

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

User · Answer

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

User · Answer

It s too late  but it may be useful for future users   You can use the below query before dropping the database query    alter database  MyDatbase  set single user with rollback immediate   drop database  MyDatabase    It will work  You can also refer to   How do I specify  quot close existing connections quot  in sql script  I hope it will help you

User · Answer

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

User · Answer

In SQL Server Management Studio 2016  perform the following    Right click on database Click delete Check close existing connections  Perform delete operation

User · Answer

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    amp  Your DB To Drop Name  amp    SET OFFLINE WITH ROLLBACK IMMEDIATE      Dim AlterCmd   New SqlCommand AlterStr  Conn       Dim DropStr As String    DROP DATABASE    amp  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    amp  vbCrLf  amp  vbCrLf  amp  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

User · Answer

Go to available databases section and select master  Then Try DROP DATABASE the DB name

User · Answer

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

User · Answer

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

User · Answer

For SQL server mgmt  studio    Right click database  Properties -  Options -  Restrict Access   Set to  Single User  and perform the drop afterwards

User · Answer

To delete a database even if it s running  you can use this batch file  echo off  set  p dbName   quot Enter your database name to drop   quot    echo Setting to single-user mode sqlcmd -Q  quot ALTER DATABASE   dbName   SET  SINGLE USER WITH ROLLBACK IMMEDIATE quot   echo Dropping    sqlcmd -Q  quot drop database  dbName  quot   echo Completed   pause

[c#] Cannot drop database because it is currently in use

Examples related to c#

Examples related to asp.net

Examples related to sql-server