Kill all connection to database

  • Comments posted to this topic are about the item Kill all connection to database

  • i often use something more simple

    DECLARE @TARGET INT

    WHILE 1=1

    BEGIN

    DECLARE KILLCONS CURSOR LOCAL FAST_FORWARD FOR

    SELECT SPID FROM MASTER..SYSPROCESSES WHERE DBID = DB_ID('DB NAME') AND SPID >=50

    OPEN KILLCONS

    FETCH NEXT FROM KILLCONS INTO @TARGET

    WHILE @@FETCH_STATUS = 0

    BEGIN

    EXEC ('KILL '+@TARGET)

    FETCH NEXT FROM KILLCONS INTO @TARGET

    END

    CLOSE KILLCONS

    DEALLOCATE KILLCONS

    END

    the infinite while is to take care of pesky applications that keep reconnecting, so you just manually break the loop when you feel like it

    --
    Thiago Dantas
    @DantHimself

  • You're right - this procedure would be much more simple if I used cursors as You did.

  • i use this:

    ALTER DATABASE Northwind SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    EXEC sp_dboption 'Northwind', 'single user', 'FALSE'

  • --Kill all users instantly

    use master

    go

    alter database test set offline with rollback immediate

    go

    alter database test set online

    go

  • Thanks for the script.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply