February 6, 2003 at 10:56 am
We are having recurring problems with a process that needs to periodically detach a database (in order to replace it with a fresh copy).
It's used for ad-hoc queries, and people leave software running that is "sticky". You can kill a process, and it just immediately tries to reconnect. Microsoft Access is a key offender (though I can't tell if it's something about it, or what our analysts are doing in it).
What we do now is kill anyone in it, put it in dbo-only mode to keep people from coming back. That works a lot of the time but not always.
If you don't kill the people in it, you get "Database state cannot be changed while other users are using the database". But even if you do, sometimes they come back so quickly you get that message anyway. We do it now in a loop and keep trying, but sometimes it is just too darn sticky.
How can one kick everyone out of a database firmly so there is no way for them to come back (while leaving SQL Server running, since this is just one database).
February 6, 2003 at 10:59 am
Do you have other databases on this server? If not, you can "pause" the server
Steve Jones
February 6, 2003 at 11:02 am
Yes, lots of them (as you can see in the last sentence). Also note that this detach and replace is being done in a SQL Agent job so we can't affect that either, I assume, and also pause the server?
What I really want is a "allow everyone now connected to finish, but don't allow new connections" or something similar. One system I use a lot had that concept for networking it had ON, SHUT and OFF. When OFF everyone went away, but when SHUT you could stay on and finish, but no new connections.
That would let us SHUT off new connections, wait until people finish (or we get impatient) and then kill them knowing no new ones could start. But I see no such concept in SQL. In fact I see no real concept for running down activity on a database (as opposed to server instance).
February 6, 2003 at 11:08 am
Pause does that. Let's everyone finish, no new connections. I think in your job, if it makes one connection, could pause the server, kill connections in the db, run the detach and attach, and unpause the server.
Steve Jones
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply