My SQL Server Programming-Kill Spid?

Here's a little snippet from an SP i wrote a couple of years ago when i wanted to drop a db. Given the id of the database (@DR_ID), killed my processes just fine...
Get the dbid using the db_id() function.

    -- Get a cursor with the processes that have to die in order to be able to drop db
    DECLARE curProcesses CURSOR
        LOCAL
        FAST_FORWARD
        READ_ONLY
    FOR
        SELECT spid
        FROM
            Master..sysprocesses
        WHERE
            dbid = @nDR_ID

    OPEN curProcesses

    FETCH NEXT FROM curProcesses INTO --Gets the first process
        @nKillProcess
    SET @nFetchStatus = @@FETCH_STATUS

    --Kill the processes
    WHILE @nFetchStatus = 0
    BEGIN
        SET @sTemp ='KILL ' + CAST(@nKillProcess as varchar(5))
        EXEC(@sTemp)
        FETCH NEXT FROM curProcesses INTO --Gets the next process
            @nKillProcess
        SET @nFetchStatus = @@FETCH_STATUS
    END
    CLOSE curProcesses
    DEALLOCATE curProcesses

No comments:

Post a Comment

Please Provide your feedback here