Tuesday, May 22, 2007

USP_KillAllProcesses - Procedure to Kill connections to (processes running in) a database

I thought this would be useful to run before restore jobs.
It uses sysprocesses to find and kill any open processes for the supplied database name or all if you pass '/all'
ALTER PROCEDURE [Utils].[usp_KillAllProcesses] @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 
 -- Fetch number of processes to kill
 IF LOWER(LTRIM(RTRIM(@database))) = '/all'
        BEGIN
    SELECT  @ins_initialprocesses = COUNT(*)
    FROM master..sysprocesses 
    WHERE status <> 'background' 
    -- to get the user process
    AND status in ('runnable','sleeping') 
    -- to avoid the current spid
    AND spid <> @@spid
    -- avoid system processes
    AND spid > 50
  END
ELSE
        BEGIN
    SELECT  @ins_initialprocesses = COUNT(*)
    FROM master..sysprocesses 
    WHERE   dbid = db_id(@database) 
    -- to avoid the sql process
    AND status <> 'background' 
    -- to get the user process
    AND status in ('runnable','sleeping') 
    -- to avoid the current spid
    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 <> 'background' 
     -- to get the user process
     AND status in ('runnable','sleeping') 
     -- to avoid the current spid
     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)
     -- to avoid the sql process
     AND status <> 'background' 
     -- to get the user process
     AND status in ('runnable','sleeping') 
     -- to avoid the current spid
     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 <> 'background' 
     -- to get the user process
     AND status in ('runnable','sleeping') 
     -- to avoid the current spid
     AND spid <> @@spid
     -- avoid system processes
     AND spid > 50
  END
   ELSE
  BEGIN
     SELECT  @ins_remainingprocesses = COUNT(*)
     FROM master..sysprocesses 
     WHERE   dbid = db_id(@database)
     -- to avoid the sql process
     AND status <> 'background' 
     -- to get the user process
     AND status in ('runnable','sleeping') 
     -- to avoid the current spid
     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)) + ' connection(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)) + ' connection(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

No comments: