locked
Close All open Connection particular Database in Sql server RRS feed

  • Question

  • hi,

    use the below Query,

    Dear All

    /* ---------------------------------------------

    Closing all the Connection on specified Database

    --------------------------------------------- */

    CREATE TABLE #TmpWho

    (spid INT, ecid INT, status VARCHAR(150), loginame VARCHAR(150),

    hostname VARCHAR(150), blk INT, dbname VARCHAR(150), cmd VARCHAR(150),request_id INT)

    INSERT INTO #TmpWho

    EXEC sp_who

    DECLARE @spid INT,@tString VARCHAR(15),@dbname VARCHAR(50)

    SET @dbname='DatabaseName';

    DECLARE @getspid CURSOR

    SET @getspid =   CURSOR FOR

    SELECT spid

    FROM #TmpWho

    WHERE dbname = @dbname

    OPEN @getspid

    FETCH NEXT FROM @getspid INTO @spid

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @tString = 'KILL ' + CAST(@spid AS VARCHAR(5))

    EXEC(@tString)

    FETCH NEXT FROM @getspid INTO @spid

    END

    CLOSE @getspid

    DEALLOCATE @getspid

    DROP TABLE #TmpWho

    GO

    Saturday, July 21, 2012 5:24 AM

Answers