Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Unhappy Unanswered: 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
    Dakki

  2. #2
    Join Date
    Mar 2004
    Location
    Austria
    Posts
    4

    Re: Force users/connections to disconnect from db

    Originally 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

  3. #3
    Join Date
    Mar 2004
    Posts
    3

    Re: Force users/connections to disconnect from db

    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

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    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.

  5. #5
    Join Date
    Mar 2004
    Posts
    3
    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 ...

  6. #6
    Join Date
    Feb 2004
    Location
    india
    Posts
    67

    Re: Force users/connections to disconnect from db

    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...

  7. #7
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Take help from this link which includes the script to kill are users that are connected to a database that needs to be dropped/detached etc. etc.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  8. #8
    Join Date
    Sep 2003
    Posts
    522
    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:
    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •