Force users/connections to disconnect from db

Hello,
I need to attach and detach 50 DB's, for which I have wrote a simple script. How can I make sure that there are no connections or users connected to the DB's, if there any users how can I forcefully disconnect them.
Thanks
DakkiOriginally posted by Dakki
Hello,

I need to attach and detach 50 DB's, for which I have wrote a simple script. How can I make sure that there are no connections or users connected to the DB's, if there any users how can I forcefully disconnect them.

Thanks
Dakki

sp_detachDB does detach a db even if users are logged in. Only if transactions are running, dettach will fail. sp_who shows you for every db on this server all logged in users.

Hope this helps
Peter|||Originally posted by peterdbd
sp_detachDB does detach a db even if users are logged in. Only if transactions are running, dettach will fail. sp_who shows you for every db on this server all logged in users.

Hope this helps
Peter

Excellent, I shall try this...

many thanks

dakki|||peterdbd,

i don't think it's an accurate statement, because you'll get this error if there is at least 1 connection open for the database you're trying to detach, even if this connection has not performed a single operation:

Server: Msg 3701, Level 16, State 1, Line 1
Cannot detach the database 'db_name' because it is currently in use.|||guru, you are right, I had to re run the script, since some of the db's had connections to it...

thanks again, to you both ...|||list all users with sp_who2
and find out sid of all the db u r looking at

using sa , kill all sid

eg:- kill sid

then proceed with detach.

Note:-
Do not any sid doing updates...|||Take help from this link (http://www.sql-server-performance.com/q&a37.asp) which includes the script to kill are users that are connected to a database that needs to be dropped/detached etc. etc.:cool:|||there are 3 problems with the script:

- based on stored procedure. i wouldn't recommend leaving such tool so handy

- based on cursor, - simply no need

- does not take into account the possibility of attempting to kill yourself (not that you'll succeed though)

there is a simpler way:

declare @.cmd varchar(100)
while (select count(*)
from master.dbo.sysprocesses (nolock)
where spid != @.@.spid and db_name(dbid) = 'your_db_name') > 0 begin
set @.cmd = 'kill ' +(select cast(min(spid) as varchar(25))
from master.dbo.sysprocesses (nolock)
where spid != @.@.spid and db_name(dbid) = 'mci2k')
exec ( @.cmd )
end