Kill 2 databases connections

  • Tac11

    SSCertifiable

    Points: 6801

    Hi Gurus,

    Below script kills the only connection related to the AdventureWorks databases. If I want to add another database to kill such as "Northwind' how do I add below?

    I tried with :

    Set @dbname = 'AdventureWorks' , 'Northwind'

    Set @dbname in ( 'AdventureWorks' , 'Northwind' )

    but no luck!!

    use master

    go

    Declare @dbname sysname

    Set @dbname = 'AdventureWorks'

    Declare @spid int

    Select @spid = min(spid) from master.dbo.sysprocesses

    where dbid = db_id(@dbname)

    While @spid Is Not Null

    Begin

    Execute ('Kill ' + @spid)

    Select @spid = min(spid) from master.dbo.sysprocesses

    where dbid = db_id(@dbname) and spid > @spid

    End

    GO

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21197

    I use this script to kill all connections to a database - modify it to your hearts content

    DECLARE @spid int
    DECLARE @strsql NVARCHAR(100)
    DECLARE curs1 CURSOR FOR SELECT spid FROM master.dbo.sysprocesses WHERE dbid=10
    OPEN curs1
    FETCH NEXT FROM curs1 INTO @SPID
    WHILE @@FETCH_STATUS=0
    BEGIN
    SET @strsql='kill '+CONVERT (VARCHAR(10),@spid)
    --print @strsql --just to check
    --EXEC (@strsql) --uncomment this if you want to execute
    FETCH NEXT FROM curs1 INTO @SPID
    END
    CLOSE curs1
    DEALLOCATE curs1

    don't forget to change the databaseid

    MVDBA

  • Mr. Brian Gale

    SSC-Insane

    Points: 22441

    I think what you are trying to do using the method you are trying is not possible.  You cannot store more than 1 value in a variable except a table variable.  You could do it with some dynamic SQL which has potential for problems and has some risks, or if you only have 2 databases you want to run it against, just add in a second variable.

    If you are wanting to do it on multiple databases, I would put it into a table.

    Or use the script provided by MVDBA (Mike Vessey).

    Do be careful killing connections though... it will cause the transaction to be rolled back.

Viewing 3 posts - 1 through 3 (of 3 total)

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