Kill all Processes in a Database

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'

*/