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'
*/
|