Kills all connections active on a given database

sp_dbkill.sql

use master
go
if object_id('sp_dbkill') is not null
	drop proc sp_dbkill
go
/*
**	- synopsis -
**	Kills all connections active on a given database
**
**	- business requirement -
**	To perform certain operations on a database it is
**	sometimes requisite that no active user connections
**	exist on the database.  This procedure discovers
**	such connections and performs the necessary kills
**
**	- syntax -
**	@db - the name of the database for which to
**		kill connections
**	@pgm (optional) - if provided, allows filtering
**		the connections killed by their 'program name'
**
**	- marginalia -
**	The procedure avoids killing the connection on which
**	it was invoked, thus it is most convenient to make said
**	invocation from any database other than the one meant
**	to be killed.
**
**	Additionally, the procedure avoids attempting to kill
**	any process in the middle of a transaction rollback.
**
**	Please do also note that clients that re-establish their
**	connections may prove problematic (see TODO list below)
**
**	- TODO -
**	1. set database to single user mode after connections
**	have been killed and retry on failure
**
**	- exempli gratia -
**	To creata a snapshot for the PMQ/JM development environment:
**
**		exec sp_create_snapshot 'te_3e_pmqjmdev'
**
**	To display the commands needed to create a snapshot:
**
**		exec sp_create_snapshot 'te_3e_pmqjmdev', @exec = 0
**
**	- metadata -
**	Author: Erick Calder <[email protected]>
**	Dept: Systems Engineering
**	Wiki: http://la-elitedocs.elitecorp.com/wiki/index.php?title=sp_create_snapshot
*/
create proc sp_dbkill
	@db sysname = null
,	@pgm nchar(256) = null
,	@restrict bit = 0
as
	declare @s varchar(max)
	
	declare spin
	cursor for
	select spid
	from master..sysprocesses
	where spid != @@spid
	and dbid = isnull(db_id(@db), dbid)
	and program_name = isnull(@pgm, program_name)
	and status != 'rollback'
	
	open spin
	declare @spid int
	while 1=1
		begin
		fetch spin into @spid
		if @@error != 0 or @@fetch_status < 0
			break
		select @s = 'kill ' + convert(varchar(max), @spid)
		exec(@s)
		end
	deallocate spin
	
	if @restrict = 1
		begin
		set @s = 'ALTER DATABASE ' + @db
		+		' SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE'
		exec(@s)
		end
go