November 11, 2002 at 10:18 am
Did you try my suggestion?
I'm able to take a db Offline when there are users connected to it the way I told you before.
I'm using SQL Server 2000 though.
You probably don't have the administrative privileges.
And if you are trying to kill the database why are you warrying about the people connected to it?
If it still wouldn't work, then stop the services on SQL Server, detach the database, then delete this database .mdf and .ldf files, and start the services. It will take only couple minutes to do.
November 19, 2002 at 2:43 pm
Got to looking at the documentation and I understand we are misreading it now. When it says
quote:
The KillDatabase method drops a database from the referenced Microsoft® SQL Server™ 2000 installation, regardless of the status or availability of the database.
If you check out the remarks it states
quote:
The Remove method of the Database object and Databases collection drops a referenced database. A database drop can fail if the database is offline. When the Remove method of the Database object or Databases collection fails, use the KillDatabase method to force a drop of the database.
This means KillDatabase should be used when the Remove method to drop a database fails and leaves it in a offline state. Unfortunately the documentation leaves you the room to infer you can kill with connections. I have submitted a documentation error to MS to get their feedback or at least their documenter looking at their wording.
November 25, 2002 at 9:34 am
Why is it the "Drop Database" command always seems to work flawlessly ... especially right after I've just keyed in the wrong database name? I've never been lucky enough to have an active connection spare me from it yet. Not once. Never before have I ever wanted an "Undo" button or command more than right then to reverse those brief "Shtoopy moments of greatness."
- Bit
November 25, 2002 at 10:07 am
AER. Sorry I said Alex, but I meant you. Yes, I have tried that. It does not work. I am using SQL Server 2000 SP2. You cannot drop a database if there are active connections by any method. Period. Yes, I have the appropriate permissions. I am sa. You cannot take a database offline while there are connections, to try your sp_detach_db method either. And shutting down SQL Server is NOT an option. This server is live.
DBA@AMC, I don't know how you are doing this but is there a special command you are using on your drop database statement? What is the syntax you are using?
Antares686, Yes I see what you are referring to now. It does not say that the killdatabase method should work regardless of connections. Although, by it's name one would assume that kill means kill. If I'm going to kill someone I'm not going to say, "Excuse me but are you busy? I would like to kill you when you get a minute." This is frustrating.
November 25, 2002 at 10:51 am
Yes, I put this into the MS folks as I hear there may be some documentation updates in the upcoming in SP3. Hopefully this is true and they can get a better statment out on this.
November 25, 2002 at 11:09 am
Well let's hope that Yukon at least has an administrative stored procedure or method to drop databases with active connections. That's one of the nice things about Oracle, is that if I am the administrator and I tell the database to do a shutdown immediate or shutdown abort, it does just that. It shuts down. But I guess they are making the assumption that their DBA's know what they are doing, a luxury Microsoft does not supply us.
I'll be very interested in seeing some of the enhancements and improvements made in Yukon. With any luck, my manager will agree to foot the bill to send me to the user conference. It's 4 days though :-\ .
November 25, 2002 at 1:09 pm
Unsavory,
You are right about active connections. I realized that in few cases before when I took my database offline there was no connections present at that time. Sorry about that.
And I understand that you cannot stop the services on the live server.
It looks that in your case the best thing will be to kill all the processes associated with this database in the Current Activity Process Info screen.
And after that you can take your database offline and follow the advise in my previous replies.
I was playing around trying to kill few processes using a Query Analyzer but it looks like you cannot modify system tables even utilizing a sp_configure stored procedure. So, you have to do it manually.
Other than that I don't have any suggestions.
Alex
November 25, 2002 at 2:45 pm
Thanks Alex,
Yeah the idea mentioned earlier actually worked the best. That idea was to set the database into single user mode with rollback immediate. That does place it into single user mode regardless of who is connected. From there, the database can be dropped after killing your own session. This is what I ended up doing to solve the problem:
-- Set database to single user mode.
USE dummy
ALTER DATABASE dummy
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
-- Drop the user our apps user to connect.
USE dummy
EXEC sp_dropuser 'people'
GO
-- Kill any active connections including my own.
USE master
declare @strPID varchar(10)
declare crsProc cursor fast_forward for
SELECT cast(p.spid as varchar(10))
FROM master..sysprocesses
JOIN master..sysdatabases d ON p.dbid = d.dbid
WHERE d.name = 'dummy'
open crsProc
fetch next from crsProc into @strPID
while @@fetch_status = 0
begin
'kill ' + @strPID
execute ( 'kill ' + @strPID )
fetch next from crsProc into @strPID end
close crsProc
deallocate crsProc
GO
-- Drop the database.
USE master
DROP DATABASE dummy
GO
Seems to be working so far. But it was a little bit of a pain to figure out. Kill(dbname) or something like it would have been easier.
Edited by - unsavory on 11/25/2002 2:47:54 PM
Edited by - unsavory on 11/25/2002 2:49:52 PM
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply