Kill Database

[dzdncnfsd]

Does anyone know how to "kill" a database? I read in BOL that the syntax is object.killdatabase(database name), but I am not sure what "object" refers to. I cannot stop/start SQL services because of some mission critical databases on the same server, so I am thinking of using this method to drop a database that is in an unusable state. Thanks for any advice offered.
Gladys
Gladys Clemmer
gladys.clemmer@goldkist.com

 

[carrr]

Gladys,

There are two ways to eliminate the problem that I know of. Both are fairly simple and final 9i.e. there's no bringing it back).

Use Enterprise Manager, right click your target database, and delete.

or

Use Query Analyzer, type in: DROP DATABASE database name

Hope this solves the problem,

carrr

 

[dzdncnfsd]

Thanks, Carrr. Tried both of those, but the problem is there are a couple of processes running against the database that have put it in a "busy" state, so the normal procedures won't work right now. A manual backup of the database quit responding, so we cancelled it, and now we cannot kill the processes that were running when the backup got cancelled. Our only choices are to stop/start SQL Services or kill the database. Stop/start is not an option during production hours.
Thanks.
Gladys Clemmer
gladys.clemmer@goldkist.com

 

[tlbroadbent]

object.killdatabase(dbname) is a SQL DMO method. DMO is an API that can be used in variuos applications to administer SQL Server. You can read the details in SQL BOL.

Can you kill the SPIDs that are using the database?

Example: Kill connection using system processor ID 16

Kill 16 Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[dzdncnfsd]

Hi, Terry.
No, we could not kill the process that was in rollback status. We finally just stopped and started SQL services to reset everything. This whole thing started because my DTS package was logging all transactions, which is unnecessary for this particular situation, and the log file finally got full. I have now set the package to not log transactions, so we won't have to worry about it again.
Also, I guess what you are saying is that the KillDatabase method is not a one-liner that can be used in Query Analyzer to get rid of a non-responsive database?
Thanks,
Gladys

Have you been to any of the games?
Gladys Clemmer
gladys.clemmer@goldkist.com

 

[tlbroadbent]

You can use SQL DMO within an SP but you would need to create an OLE Automation object. It is much easier to use straight forward SQL commands. I have used SQL DMO in VB to script databases and to test if servers are running.

We have not attended any of the Olympic Sports events. The tickets were too expensive. We have attended a few of the Cultural Olympiad event. That has been fun. Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com