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