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'

*/