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 Friday, July 2, 2010 2:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 2, 2010 2:30 AM
Points: 1, Visits: 0
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);
}
Post #946658
Posted Friday, July 2, 2010 1:07 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
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
Post #947052
Posted Saturday, July 31, 2010 2:06 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 8, 2012 5:07 PM
Points: 4, Visits: 25
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.

Regards, Ira Grollman, OMKT LLC



'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

Post #961805
Posted Saturday, July 31, 2010 7:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, August 2, 2014 3:24 PM
Points: 4, Visits: 158
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.
Post #961835
Posted Thursday, February 14, 2013 1:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 1:16 PM
Points: 1, Visits: 0
Nice article on same issue is discussed in this link.
http://gilgh.com/article/unable_to_drop_database,_Currently_in_Use
Post #1420273
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse