USP_KillHostProcesses

CREATE PROCEDURE [dbo].[USP_KillHostProcesses] @HOSTNAME varchar(100)
AS

SET NOCOUNT ON

-- 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

SELECT @ins_initialprocesses = COUNT(* )
FROM   MASTER..SYSPROCESSES
WHERE  HOSTNAME = @HOSTNAME
AND  SPID <> @@spid
select *  FROM   MASTER..SYSPROCESSES

-- Check there are processes to kill
IF @ins_initialprocesses > 0
BEGIN
  -- Build command string of processes to kill >
  SELECT @chv_killprocessestring = COALESCE(@chv_killprocessestring,',') + 'KILL ' + CONVERT(VARCHAR,SPID) + '; '
  FROM   MASTER..SYSPROCESSES
  WHERE  HOSTNAME = @HOSTNAME

  -- Kill processes (run command created above) >
  EXEC( @chv_killprocessestring)
  
  -- Fetch number of processes remaining >
  SELECT @ins_remainingprocesses = COUNT(* )
  FROM   MASTER..SYSPROCESSES
  WHERE  HOSTNAME = @HOSTNAME
  
  IF @ins_remainingprocesses = 0
    BEGIN
      -- Report on processes killed if killed them all >
      PRINT LTRIM(RTRIM(UPPER(@HOSTNAME))) + ' - 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(@HOSTNAME))) + ' - 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 @HOSTNAME + ' - No processes to kill'
END

GO