Loading ...

Kill all processes by database name instead of spid | CodeAsp.Net

Kill all processes by database name instead of spid

 /5
0 (0votes)

How often you have to kill processes of a database and run "sp_who2" command and then "kill <spid>" to kill each processes? This is a tedious task. I have written a Stored procedure which you can run in your "master" database and can use it to kill all processes of a database. You need to supply database name only to this stored procedure.

CREATE PROC [Kill_All_Processes]
    @DatabaseName VARCHAR(100)
AS 
    BEGIN

        DECLARE @SPID VARCHAR(10);
        DECLARE @Command VARCHAR(200);
 
 
        DECLARE SpidCursor CURSOR
        FOR
            SELECT  [SPID]
            FROM    master.dbo.sysprocesses
            WHERE   [dbid] = DB_ID(@DatabaseName)
                    AND [SPID] != @@SPID
 
        OPEN SpidCursor
 
        FETCH NEXT FROM SpidCursor INTO @SPID
 
        WHILE @@FETCH_STATUS = 0 
            BEGIN
                PRINT 'Killing process: ' + RTRIM(@SPID);
                SET @Command = 'kill ' + RTRIM(@SPID) + ';';
                PRINT @Command;
                EXECUTE(@Command);
                FETCH NEXT FROM SpidCursor INTO @SPID
            END
 
        CLOSE SpidCursor
        DEALLOCATE SpidCursor

    END

Usage:

EXEC [Kill_All_Processes] 
@DatabaseName = 'YourDBName' -- varchar(100)

Comments (no comments yet)

Top Posts