locked
DROP DATABASE -> database in use error RRS feed

  • Question

  • when I do DROP DATABASE I'm getting errors because it's in use.  But if I do a delete from the mgmt studio gui I have the option to close all connections as part of the delete.  Is there a way to achieve the same effect from a sql script?
    Wednesday, July 12, 2006 4:05 AM

Answers

  •  

    There are scripts that you can get to kill all users in the database... it uses a simple cursor to collect the information from the sysprocesses table or the sp_who command and run a kill command against each of the connections that releate to the database in question. If you have a look at the scripts section of www.sqlservercentral.com you will be able to find the scripts i am talking about.

    Wednesday, July 12, 2006 4:27 AM
  • Here is an article I wrote about that:

    http://drsql.spaces.msn.com/blog/cns!80677FB08B3162E4!950.entry

    Basically:

    alter database <databaseName>
           set single_user with rollback immediate
     
    I find this indispensible when testing database creation scripts with a few windows open and I need to drop the database, recreate it with scripts, fix some stupid bug and then re-run things.
     
    Two tricks:
     
    First, most every dialog in SSMS and SSMSE (express edition) have a script button.  So most things you can just get the script.  On my express edition however, the alter database stuff wasn't scripted, so I cannot say with 100% accuracy (it might just be a bug :)

    Second, a trick to find these things sorts of things out.  If you aren't using Express Edition, you can run profiler and usually see the commands that SQL Server is running.  This way, if the script doesn't work like you expect it to, you can see what they are actually running :)

     

    Wednesday, July 12, 2006 4:26 AM

All replies

  • Here is an article I wrote about that:

    http://drsql.spaces.msn.com/blog/cns!80677FB08B3162E4!950.entry

    Basically:

    alter database <databaseName>
           set single_user with rollback immediate
     
    I find this indispensible when testing database creation scripts with a few windows open and I need to drop the database, recreate it with scripts, fix some stupid bug and then re-run things.
     
    Two tricks:
     
    First, most every dialog in SSMS and SSMSE (express edition) have a script button.  So most things you can just get the script.  On my express edition however, the alter database stuff wasn't scripted, so I cannot say with 100% accuracy (it might just be a bug :)

    Second, a trick to find these things sorts of things out.  If you aren't using Express Edition, you can run profiler and usually see the commands that SQL Server is running.  This way, if the script doesn't work like you expect it to, you can see what they are actually running :)

     

    Wednesday, July 12, 2006 4:26 AM
  •  

    There are scripts that you can get to kill all users in the database... it uses a simple cursor to collect the information from the sysprocesses table or the sp_who command and run a kill command against each of the connections that releate to the database in question. If you have a look at the scripts section of www.sqlservercentral.com you will be able to find the scripts i am talking about.

    Wednesday, July 12, 2006 4:27 AM
  • I've been trying to drop an in-use database.  I tried the above "Alter database" statement that everyone keeps citing.  All it does is set the database to allow only a single-user, and I was the only user of the database (I'm doing testing - eventually, yes, others will use this database, but for now, setting it to single-user mode doesn't do jack for me).  In my case, it didn't solve my problem because I was still, somehow, logged into it, even though I had closed all previous SqlConnections prior to this part of my code.  I've even tried using

    SqlCommand sqlDBCheck = new SqlCommand();  
    sqlDBCheck.Connection = sqlConn;  
    sqlDBCheck.CommandText = "DECLARE @command NVARCHAR(500) " +  
                             "DECLARE @spid INT " +  
                             "DECLARE active_spids CURSOR LOCAL " +  
                             " FOR SELECT s.spid FROM master.dbo.sysprocesses s " +  
                             " WHERE (s.dbid = (SELECT dbid FROM master.dbo.sysdatabases WHERE (name = '#1#'))) " +  
                             "OPEN active_spids " +  
                             "FETCH NEXT FROM active_spids INTO @spid " +  
                             "WHILE (@@fetch_status = 0) BEGIN " +  
                             " SET @command = 'KILL ' + CONVERT(VARCHAR(10), @spid) " +  
                             " EXECUTE ( @command ) " +  
                             " FETCH NEXT FROM active_spids INTO @spid " +  
                             "END " +  
                             "DEALLOCATE active_spids ";  
                              
    sqlDBCheck.ExecuteNonQuery(); 

    and this did nothing for me, either, though it did complete successfully.  I got it from http://www.codecomments.com/archive343-2005-2-396030.html via http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21755283.html.  It was taken from some vague VB, so I'm not sure if my implementation of it in C# is accurate.  There is no "ExecuteWithEvents" method on a SqlCommand or SqlConnection in C#, so I'm not sure what they were going for.  After going through the trouble of importing Microsoft.SqlServer.ConnectionInfo and .Smo namespaces, using a "ServerConnection" and "Server" didn't yield anything, either.

    Is there a way to just kill all connections, processes, and users on a database so it can be dropped?  Please post if so!

    Thanks in advance,
    Tom


    Thursday, January 15, 2009 12:17 AM
  • Found out the reason that code did nothing for me is because you can't kill your own process.

    I had the SqlConnection connection string with an Initial Catalog of master and still could not drop the database.
    I tried SqlConnection.ClearAllPools() and this did nothing.
    I tried pooling=false on the connection string, on both the old database and master SqlConnection connection strings, as suggested in one forum, no dice.
    I tried using the "single_user" alter database statement everyone's been so fond of in these forums, just for kicks - doesn't do anything except send your database to single user mode.  When you are already the only user on the database, that doesn't exactly help.

    So now that I've told everyone what doesn't work, at least when the database in question is your default database.......

    You have to re-assign your default database to something else, like master.


    ALTER
     LOGIN [DOMAIN\myaccount] WITH DEFAULT_DATABASE=master

    Hope this helps someone.

    -Tom
    • Proposed as answer by navyjax2 Thursday, January 15, 2009 6:02 PM
    • Edited by navyjax2 Thursday, February 12, 2009 1:34 AM code error
    Thursday, January 15, 2009 6:02 PM
  • Dealt with the same problem today, and found that a combination of two approaches (including that of Louis Davidson), worked for me on an SQL2008R2 Express and Standard Edition:

    USE master
    GO
    ALTER DATABASE <db_name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    DROP DATABASE <db_name>
    GO
    
    So the trick seems to be to "switch away" from the database you're about to delete, then kill any other connections except your own (if any), then drop succeeds.
    Wednesday, October 24, 2012 8:10 AM