Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to force drop database? Expand / Collapse
Author
Message
Posted Monday, June 2, 2008 7:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 19, 2008 1:35 AM
Points: 7, Visits: 15
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
Post #509824
Posted Monday, June 2, 2008 8:20 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 7:32 PM
Points: 15,517, Visits: 27,895
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #509887
Posted Monday, June 2, 2008 8:26 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, Visits: 711
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
Post #509890
Posted Monday, June 2, 2008 8:37 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 7:32 PM
Points: 15,517, Visits: 27,895
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #509899
Posted Monday, June 2, 2008 11:23 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 4, 2009 7:34 AM
Points: 129, Visits: 130
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)
Post #510091
Posted Monday, June 2, 2008 4:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 19, 2008 1:35 AM
Points: 7, Visits: 15
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 :D
Post #510335
Posted Tuesday, June 3, 2008 5:24 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 7:32 PM
Points: 15,517, Visits: 27,895
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #510559
Posted Tuesday, June 3, 2008 8:13 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 4, 2009 7:34 AM
Points: 129, Visits: 130
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)
Post #510687
Posted Tuesday, June 3, 2008 1:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 19, 2008 1:35 AM
Points: 7, Visits: 15
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

[i want to say that i really new using SQL 2005 and ASP.Net, so maybe there are other way, maybe more easy to do it...., for now this one is working... :D]
Post #510915
Posted Wednesday, January 14, 2009 5:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 2, 2010 12:59 PM
Points: 2, Visits: 3
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
Post #636774
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse