Technical Article

Kill Database sessions

,

usage:

execute KillDatabaseSessions_SP @DBName ='NorthWind'

CREATE PROCEDURE KillDatabaseSessions_SP ( @DBName VARCHAR(50) )
AS 

            DECLARE @SessionID INT
            CREATE TABLE #DBSessions
                (
                  SPID INT,
                  status VARCHAR(50),
                  loginname VARCHAR(50),
                  hostname VARCHAR(100),
                  blk VARCHAR(10),
                  dbname VARCHAR(100),
                  cmd VARCHAR(50),
                  CPUTime INT,
                  DiskIO INT,
                  lastbatch VARCHAR(50),
                  programname VARCHAR(100),
                  SPID2 INT,
                  requestid INT
                ) ;
            INSERT  INTO [#DBSessions]
                    (
                      [SPID],
                      [status],
                      [loginname],
                      [hostname],
                      [blk],
                      [dbname],
                      [cmd],
                      [CPUTime],
                      [DiskIO],
                      [lastbatch],
                      [programname],
                      [SPID2],
                      [requestid] 
                    )
                    EXEC sp_who2 ;
                    IF( (SELECT  COUNT(SPID) FROM    [#DBSessions] WHERE   upper(dbname) = UPPER(@DBName))>0)
                    BEGIN
            DECLARE ctr CURSOR
                FOR SELECT  SPID
                    FROM    [#DBSessions]
                    WHERE   upper(dbname) = UPPER(@DBName) ;

            DECLARE @string VARCHAR(1000)

            OPEN ctr
            FETCH NEXT FROM ctr INTO @SessionID
            WHILE( @@FETCH_STATUS = 0 )
                BEGIN
                    SET @string = 'KILL ' + CONVERT(VARCHAR(20), @SessionID)
                    exec ( @string )         
select @string
                    FETCH NEXT FROM ctr INTO @SessionID
                END
           
            CLOSE ctr
            DEALLOCATE ctr

            DROP TABLE [#DBSessions]
        END

Rate

1 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

1 (1)

You rated this post out of 5. Change rating