USP_KillSleepingProcesses - Procedure to kill sleeping processes

USP_KillSleepingProcesses - Procedure to kill sleeping processes
Pass the database name as the parameter or '/all' for all dbs.

exec USP_KillSleepingProcesses 'WebDb' -- or '/all'
GO

ALTER PROCEDURE [Utils].[usp_KillSleepingProcesses] @database varchar(100)
AS

SET NOCOUNT ON

-- Check validity of supplied database name
IF DB_ID(@database) > 4 OR LOWER(LTRIM(RTRIM(@database))) = '/all'
BEGIN
  -- variable declaration and initial values
  DECLARE  @chv_killprocessestring VARCHAR(8000)  
  DECLARE  @ins_initialprocesses SMALLINT
  DECLARE  @ins_remainingprocesses SMALLINT  

  SET @ins_initialprocesses = 0  
  SET @ins_remainingprocesses = 0  
  SET @chv_killprocessestring = ''
  
  -- Fetch number of processes to kill
 IF LOWER(LTRIM(RTRIM(@database))) = '/all'
        BEGIN
        SELECT @ins_initialprocesses = COUNT(* )
        FROM   MASTER..SYSPROCESSES
                      WHERE  STATUS = 'sleeping'
       AND  SPID <> @@spid
       -- avoid system processes
       AND spid > 50
        END
      ELSE
        BEGIN
        SELECT @ins_initialprocesses = COUNT(* )
        FROM   MASTER..SYSPROCESSES
                      WHERE  DBID = DB_ID(@database)
                      AND STATUS = 'sleeping'
        AND  SPID <> @@spid
  -- avoid system processes
  AND spid > 50

        END
  
  -- Check there are processes to kill
  IF @ins_initialprocesses > 0
    BEGIN
      -- Build command string of processes to kill >
      IF LOWER(LTRIM(RTRIM(@database))) = '/all'
        BEGIN
          SELECT @chv_killprocessestring = COALESCE(@chv_killprocessestring,',') + 'KILL ' + CONVERT(VARCHAR,SPID) + '; '
          FROM   MASTER..SYSPROCESSES
          WHERE  STATUS = 'sleeping'
     AND  SPID <> @@spid
           -- avoid system processes
           AND spid > 50
        END
      ELSE
        BEGIN
          SELECT @chv_killprocessestring = COALESCE(@chv_killprocessestring,',') + 'KILL ' + CONVERT(VARCHAR,SPID) + '; '
          FROM   MASTER..SYSPROCESSES
          WHERE  DBID = DB_ID(@database)
                 AND STATUS = 'sleeping'
     AND  SPID <> @@spid
           -- avoid system processes
     AND spid > 50

        END
      
      -- Kill processes (run command created above) >
      EXEC( @chv_killprocessestring)
      
      -- Fetch number of processes remaining >
      IF LOWER(LTRIM(RTRIM(@database))) = '/all'
        BEGIN
     SELECT @ins_remainingprocesses = COUNT(* )
     FROM   MASTER..SYSPROCESSES
     WHERE  STATUS = 'sleeping'
       AND  SPID <> @@spid
        -- avoid system processes
       AND spid > 50
  END
   ELSE
  BEGIN
     SELECT @ins_remainingprocesses = COUNT(* )
     FROM   MASTER..SYSPROCESSES
     WHERE  DBID = DB_ID(@database)
       AND  STATUS = 'sleeping'
    AND  SPID <> @@spid
    -- avoid system processes
    AND  spid > 50
  END
      
      IF @ins_remainingprocesses = 0
        BEGIN
          -- Report on processes killed if killed them all >
          PRINT LTRIM(RTRIM(UPPER(@database))) + ' - Killed ' + RTRIM(CONVERT(VARCHAR(10),@ins_initialprocesses)) + ' process(s)'
        END
      
      IF @ins_remainingprocesses <> 0
        BEGIN
          -- Report on processes remaining if could not end them all >
          PRINT LTRIM(RTRIM(UPPER(@database))) + ' - Killed ' + RTRIM(CONVERT(VARCHAR(10),@ins_initialprocesses - @ins_remainingprocesses)) + ' process(s), ' + RTRIM(CONVERT(VARCHAR(10),@ins_remainingprocesses)) + ' processes remaining.'
        END
    END
    
  IF @ins_initialprocesses = 0
    BEGIN
      -- Report there are no processes for that DB >
      PRINT @database + ' - No processes to kill'
    END
END

ELSE

BEGIN
  -- Supplied database name not valid or was a system db, hence report that >
  PRINT 'Invalid Database name or System Database supplied'
END
GO