killdatabase... Is it safe?
-
Two questions for everyone.
First of all, is it safe to use the killdatabase method? By safe, I don't mean what if I accidently kill the wrong one, but I mean can it harm the database server or master database itself or cause Sql Server to become unstable?
Second question:
Can someone please show me a T-SQL statement(program) to call this method and kill a database?
-
TSQL is DROP DATABASE, and if you kill the wrong database you can create issues including killing the server. If you drop the wrong one thou and is not a system database the big problem is the files are deleted (this applies to DROP DATABASE as well) and unless you have a backup you can restore it is gone. Another thing is if you have jobs or DTS packages hitting the DB you need to remove them or you will get errors. Now personally even if I am sure about dropping a database I always do a FULL database backup to be sure if I make a mistake I can recover from it.
-
Yeah I am familiar with the drop database command. I am interested in getting more information on the killdatabase method mentioned in SQL Server Books online that kills a database regardless of connections or database state. I don't care who or what is attached to it. I want it dead and I want it dead now. Development environment, mind you. = )
-
Killdatabase is a DMO method, you'd have to instantiate it either in VB code, VBScript, or usin the sp_oa~ procs.
Andy
-
Ok, we're getting closer = ). But can someone help me out with either a VB script or that sp_oa thing? I'm not an object oriented programmer so I don't understand methods, objects, etc. I just want a quick .vb script that can kill a database.
But additionally, I don't know if this will leave SQL Server in a messy state or not. Anyone know? Or will it kill it cleanly, without leaving a bunch of orphan processes laying around or hosing the master database.
-
This will do it:
Dim oserver As SQLDMO.SQLServer
'create standard server object first
Set oserver = New SQLDMO.SQLServer
With oserver
.LoginSecure = True
.Connect "yourserver"
End With
oserver.KillDatabase "yourdb"
oserver.DisConnect
Set oserver = Nothing
Wont hurt anything to use it - unless you drop the wrong db!
Andy
-
It will clean up everything except Jobs, views and processes in other DBs that referece the dropped DB and DTS packages. If you have anything in those areas you will have to handle.
-
I don't believe it!!! Microsoft lied!
The killdatabase method does NOT drop a database regardless of connections, activity or state as it clearly states in the booksonline. If there are connections, it still gives the standard error that the database cannot be dropped while there are users connected to it.
Anyone have any ideas here?
-
Hi, in an other thread(cris hedga) i found a way to set a database in single user mode even if there are active connections in it.
ALTER DATABASE kjtest
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
I tested this and it and the connections are disconnected.
When the db is in single user mode then you could drop the database.
-
quote:
I don't believe it!!! Microsoft lied!The killdatabase method does NOT drop a database regardless of connections, activity or state as it clearly states in the booksonline. If there are connections, it still gives the standard error that the database cannot be dropped while there are users connected to it.
Anyone have any ideas here?
I can believe they have the documentation wrong. I would send a bug report to them. I will also pass this along to the SP3 beta folks.
I did test it and got the same thing you did.
-
Cool. You would think that killdatabase would encapsulate the call to kill the connections.
Andy
-
OK, thanks everyone for the help. I'll try the alter database method listed above and see if that does it. The problem I am having is that PeopleSoft application servers immediately reconnect if their processes are killed. So I can kill all the processes but then they are just back in again. They connect as sa in our environment because we are developing and they need sa access for certain testing they do. So I cannot drop the user. This is why this is such a difficult thing. It just seems that a database platform as robust as Sql Server, should have a command to drop a database regardless of who is connected to it like the other big boys do. But I guess that is how Microsoft likes to manage its applications. It would seem that they assume they know better than you do and that you would never want to drop a database that someone is currently using. It's like when I want to delete a file in windows and it will not let me because someone has a lock on it. I mean come on. I'm the administrator of this system, and I want to delete this file right now. But no, can't do it. Oh sorry... 😛 Guess I'm getting off topic here. Hehehe. Please don't think I'm anti Microsoft because I'm not. I happen to love their products. Just a few little annoyances, that's all.
-
Placing the database into single user mode with rollback immediate worked. Thanks!
-
I don't know which SQL Server you are using.
If it's 2000 then you can right click on database, go to 'All Tasks', 'Take Offline'.
Then go to Query Analyzer and type this line of code:
USE master EXEC sp_MSkilldb 'yourdb'
If you are using SQL Server 7.0 then in Query Analyzer you can use this query:
EXEC sp_dboption 'yourdb','offline','true'
USE master EXEC sp_MSkilldb 'yourdb'
If this will not work you can use this code:
EXEC sp_detach_db 'yourdb'
But first take your db offline.
After you'll detach it you can just delete .mdf and .ldf files from your MSSQL directory.
May be this method looks not so great but it should work.
Best regards.
Alex
-
Thanks for the information Alex, but you cannot take the database offline if there are users connected to it. You are not allowed to change the database state with connections open to the database.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply