Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unanswered: mssql 2000 kill all user connection for restore

    i need a sql statment or procedure to kill all user connection to one database.
    And Deny new connection for a little time.
    So i can a restore this database from a dump.

    I can this do only in exclusiv modus. without any other connection.

    Can anybody help me??

    thanks and best regards from Berlin, Germany

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Code:
    declare @l_spid varchar(4)
    ,@l_hostname varchar(20)
    ,@dbname varchar(256)
    
    select @dbname = 'YOUR DATABASE NAME HERE'
    declare kill_cursor scroll cursor
    for
    select convert(varchar(4), spid), hostname from master..sysprocesses with (nolock)
    where db_name(dbid) = @dbname
    
    open kill_cursor
    select @@cursor_rows
    
    fetch next from kill_cursor into
    @l_spid
    ,@l_hostname
    while (@@fetch_status = 0 )
    begin
    select @l_hostname Killed
    exec ( 'kill ' + @l_spid)
    fetch next from kill_cursor into
    @l_spid
    ,@l_hostname
    end
    close kill_cursor
    deallocate kill_cursor
    RESTORE STATEMENT HERE
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Apr 2004
    Posts
    2

    thanks looks good

    thanks i can use the code in my procedure.

    Cu ... trapezz

  4. #4
    Join Date
    Jan 2004
    Posts
    49
    alter database DBName
    set SINGLE_USER with rollback immediate

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I prefer


    Code:
    ALTER DATABASE dbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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