Performance - Kill all Processes in a Database

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    ChillyDBA
Date:        16 May 2009

Description

This SP iterates through all user processes in a database and kills them.  The database is supplied as a parameter.
If no database is supplied, then the SP will exist with no action.
If you are calling the SP from a session connected to the target database then all connections except yours will be killed.
I could have screened out the current connection as well as the system connections, but the original use for this SP was when performing restores of a DEV environment (scheduled or immediate), and leaving it to fail for the current connection produced a more immediate error than the 30-second wait for a restore process to fail.

Code

Stored Procedure:


use master
go

DROP PROCEDURE dbo.usp_KillProcesses
GO

CREATE PROCEDURE dbo.usp_KillProcesses @DatabaseName SYSNAME


AS
/***************************************************************
Purpose:   To kill all processes in a specific database.
          
           This was primarly intended to be used when performing backups/restores/attaches/detaches,
           but can be equally useful in quiescing a database with problem connections.
           Very much a sledgehammer approach to the secondary purpose, but very useful for the priamry purpose.
          
           NOTE:  The current user connection is not excluded from the kill processing - if you are trying to restore
           then running your own process in the database you are restoring will never allow it to work - the kill process
           is threfore allowed to fail when trying to kill your own connection as this is quicker than the 30 seconds wait for a restore operation
           failure.
          
           Parameters are as follows:
                   @DatabaseName               - Database for which to kill connections.
          
Author:        ChillyDBA
History:   16 May 2009

****************************************************************/

SET NOCOUNT ON

DECLARE
    
@spid SMALLINT,
    
@dbid SMALLINT,
    
@spidStr VARCHAR(10)

IF ISNULL(@DatabaseName, '') = ''
BEGIN
   SELECT
'No Database Name parameter supplied'
  
RETURN
END
SELECT
    
@dbid = DB_ID(@DatabaseName)

DECLARE spid_curs CURSOR
    FOR SELECT
            
spid
        
FROM master..sysprocesses
        
WHERE dbid = @dbid
        
AND spid > 50
          
    
FOR READ ONLY

OPEN
spid_curs
FETCH NEXT FROM spid_curs INTO @spid

SELECT
    
'Killed Process:'

WHILE (@@fetch_status = 0)
    AND (
@@error = 0)
    
BEGIN
        SELECT
            
@spidStr = CONVERT(VARCHAR(10), @spid)
        
EXEC ('kill ' + @spidStr
            
)

        
SELECT
            
@spid

        
FETCH NEXT FROM spid_curs INTO @spid
    
END

CLOSE
spid_curs
DEALLOCATE spid_curs


SET NOCOUNT OFF
RETURN

/*
   EXEC master.dbo.usp_KillProcesses NULL
   EXEC master.dbo.usp_KillProcesses 'msdb'
  

*/

ċ
usp_KillProcesses.sql
(2k)
Andy Hughes,
Aug 23, 2012, 10:34 AM
Comments