How to force drop database?

  • Hi people,

    i'm new in SQL 2005 programing, and at this moment i'm making one website in ASP.NET/VB.NET... In that Website i have one connection or two, dependig of the case, to one or two servers... in the first server is one database that need to be corrected, so a second database it will be created to support the changes.

    Until this point everithing cool, but now, after make all i need, i have to delete the auxiliar database that i create but wen i try to drop it it say that "Cannot drop database "words" because it is currently in use."

    First i was trying to drop it from VB.net code and now with one sql server 2005 query, but nothing, always the same error.

    What i ask is if is any way to kill all conections and drop the database or simple force the drop, preferencly made from vb.net code...

    Thanks for the attencion 🙂

    Armando

  • This works, but be careful, it will cause data loss to open transactions.

    ALTER DATABASE [dbname]

    SET SINGLE_USER --or RESTRICTED_USER

    WITH ROLLBACK IMMEDIATE;

    GO

    DROP DATABASE [dbname];

    GO

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I've seen some instances where even going into single-user mode doesn't work... but if you take the DB offline it does.

    Q. When you take the DB offline does it allow the current transactions to complete?

    Mark

  • Yeah, I've seen SINGLE_USER fail too. There is a lag, however tiny, between that GO statement and the next statement in the batch. It only takes one connection to muck you up. We usually use RESTRICTED_USER because only DBA's have it, so we're the only ones that we have to worry about.

    According to BOL, setting the database to OFFLINE is "clean" which usually means that transactions are completed or rolled back prior to the change in state. I tried it out (not in production) and it worked pretty well, maybe I'll use this one instead from now on:

    ALTER DATABASE [AdventureWorks]

    SET OFFLINE

    WITH ROLLBACK IMMEDIATE

    GO

    DROP DATABASE [AdventureWorks]

    GO

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Perfact, When you try to use DB in Single user mode or readonly mode It will not allowe you to do that, because some processes still using the DB and while DB in use then you should not be able to do any one of the options.

    If it is an urgent scnerio then probably you may need to kill the processes from the QA by using SP_WHO2 ACTIVE

    Make sure you can not delete any system process or self process as well.

    Manoj

    MCP, MCTS (GDBA/EDA)

  • This solution is working, the database desapear from the SQL Server 2005...:)

    But the file aux_bd.mdf and aux_bd.LDF still in the derectory "...\Microsoft SQL Server\MSSQL.1\MSSQL\Data" and if i try to create again other db with the same name gives error.

    Is possible to delete those files too?

    (The application is portable so the path to the data derectory can change)....

    eheh, a few hours and google help:

    Select filename From master..sysdatabases Where Name = 'mydatabase'

    returns the path of mydatabase.mdf... for delete the *.ldf is possible to work withe the properties strings

    [In Vb.net is just string.replace()]

    Thanks to all for the help 😀

  • I'm not sure. When I drop the database, the files go away too. Did you detach instead of drop the database?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • That's true. it's a windows based and when you delete the DB it will ask you to confirme that r u sure want to delete in Windows and in SQL Delete will delete .mdf and .ldf files from the directory.

    Manoj

    MCP, MCTS (GDBA/EDA)

  • Grant Fritchey say:

    Yes wen your delete one database the phisical file is deleted to...

    but in my case, i have one aplication in ASP.NET, that aplication should recive the information of any server and create one database, make some operations and in the end should be deleted.

    Create is easy, but wen is the time to delete, is always saying that have some pendent connections and don't allow to delete... so i had to find one way to do it, in this case the aswer of Grant Fritchey:

    ALTER DATABASE [AdventureWorks]

    SET OFFLINE

    WITH ROLLBACK IMMEDIATE

    GO

    DROP DATABASE [AdventureWorks]

    GO

    Allow my asp.net to remove the database, even if there are some connections.... But the phisic files still on the data folder of SQL program, and the application is portable, so depending of the server, the path can be different.

    So to find it:

    Select filename From master..sysdatabases Where Name = 'mydatabase'

    And after the code of the on top. But for each database there are to files, one with mydb.mdf extension and other with mydb.ldm, the query on top returns the one with *.mdf, the other with VB string tratment is easy to have... so:

    - 1st i select the filename

    - 2st i delete the database

    - 3st i delete the phisic files of database

  • Well, normally "DROP DATABASE" will also delete the physical files. In my case, I drop all tables first (which succeeds), then try to drop the database (which doesn't).

    The variation of the "ALTER DATABASE" command that was in this post, which is close to what I've seen floating around the web, didn't help. After using

    alter database MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    it said

    ALTER DATABASE statement failed.

    Cannot open database "MyDatabase" requested by the login. The login failed.

    Login failed for user 'DOMAIN\myaccount'.

    when I tried to run the DROP DATABASE statement. And then, when using the one I found on this site:

    alter database MyDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE

    It still says:

    Changes to the state or options of database 'MyDatabase' cannot be made at this time. The database is in single-user mode, and a user

    is currently connected to it.

    ALTER DATABASE statement failed.

    Cannot open database "MyDatabase" requested by the login. The login failed.

    Login failed for user 'DOMAIN\myaccount'.

    Is there a command to log yourself off of a database so it can be dropped? My account has all the privileges it needs - I'm a local Admin on the SQL box - and I'm the only one connected to the database - I know, since I'm doing testing and running a C# console app that connects & disconnects with the database as it finds and updates data. All of those SqlConnections are closed by the time I run this code to drop the database, however, which happens as part of the uninstall routine. I'd like it to programmatically drop/kill/delete the database, not have to manually restart SQL server and then have to manually delete it (which is where I'm at now, and have verified this works).

    Thanks,

    Tom

  • I had the same problem too...

    Situation is resolved and working when executing the following SQL statement within a try/catch (Im doing this through C#)

    string sqlStrDrop = "USE "+databaseName+" ALTER DATABASE "+databaseName+" SET SINGLE_USER WITH ROLLBACK IMMEDIATE USE master DROP DATABASE " + databaseName;

    SqlCommand dropIt = new SqlCommand(sqlStrDrop, myConnection);

    try

    {

    dropIt.ExecuteNonQuery();

    info_TextBlock.AppendText("Database dropped!" + Environment.NewLine);

    log.WriteLogMessage("Database dropped!" + Environment.NewLine, logFile);

    }

    catch (Exception ex)

    {

    MessageBox.Show(ex.ToString(), "AMS6 Boot Strap: Error!", MessageBoxButton.OK, MessageBoxImage.Information);

    }

  • What I ended up having to do was actually:

    SqlConnection.ClearAllPools();

    Then assign a SqlCommand to drop the table like I was originally doing:

    string sqlConn = ""; // your SQL Connection String goes here

    string sqlDropTable = "If (object_id ('MyDB.dbo.MyTable') is not null) DROP MyDB.dbo.MyTable";

    SqlCommand sqlDropCmd = new SqlCommand(sqlDropTable, sqlConn);

    sqlDropCmd.ExecuteNonQuery();

    -Tom

  • Sorry, I don't visit here that often. This is what I use and yes, there is a small timing window that someone could reestablish a connection. You could check the db.activeconnections and if it is greater than zero, retry the sequence. The offline clears active connections "gracefully" and the online seems to tell SQL that it can take control of the database filegroups and files again. Without the db.online the files are not deleted.

    [p]Regards, Ira Grollman, OMKT LLC[/p]

    'requires references in your project to the two dll's that these live in

    Imports Microsoft.SqlServer.Management.Common

    Imports Microsoft.SqlServer.Management.Smo

    'pick whatever server you are using...

    Dim smoServer As Server = New Server(".\SQLExpress")

    Public Function blnDropDatabase(ByVal strName As String, ByVal smoServer As Server) As Boolean

    For Each db As Microsoft.SqlServer.Management.Smo.Database In smoServer.Databases

    If LCase(db.Name) = LCase(strName) Then

    Try

    db.SetOffline()

    db.SetOnline()

    db.Drop()

    Catch ex As Exception

    'Unable to drop

    Return False

    End Try

    Return True

    End If

    Next

    Return False

    End Function

  • Simple way is make Database in Single user mode and restore it.

    Use the Alter database command set db option to Single user and restore DB.

  • Nice article on same issue is discussed in this link.

    http://gilgh.com/article/unable_to_drop_database,_Currently_in_Use

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply