Thursday, January 25, 2007

Kill Processes Cursor

Kill all processes running by a user >
DECLARE KILLUSERPROCESS CURSOR FORWARD_ONLY FOR
SELECT SPID
FROM   SYSPROCESSES
WHERE  LTRIM(RTRIM(LOGINAME)) = 'login'

DECLARE  @intSPID INT
DECLARE  @chvDSQL VARCHAR(128)

OPEN KILLUSERPROCESS
FETCH NEXT FROM KILLUSERPROCESS INTO @intSPID

WHILE (@@FETCH_STATUS = 0)
  BEGIN
    IF EXISTS (SELECT SPID
               FROM   SYSPROCESSES
               WHERE  LTRIM(RTRIM(LOGINAME)) = 'login'
                      AND SPID = @intSPID)
      BEGIN
        SET @chvDSQL = 'KILL ' + CAST(@intSPID AS VARCHAR)        
        EXEC( @chvDSQL)
      END
    
    FETCH NEXT FROM KILLUSERPROCESS INTO @intSPID
  END

CLOSE KILLUSERPROCESS
DEALLOCATE KILLUSERPROCESS

No comments: