Kill ALL Connections To a SQL Database

The Kill Connection script utilizes sys.dm_tran_locks (syslockinfo for SQL 2000) to capture SPIDs that are accessing the specified database. The traditional way of using sysprocesses table alone can not detect distributed transactions or cross-database references to the database.


Create Proc [dbo].[usp_killConnections]
@db_name Nvarchar(200)
AS
set nocount on
if db_id(@db_name) is null
return

declare @spid int
declare spid cursor for

/* For SQL 2000 and SQL 2005/2008 Backward compatible mode*/

select spid from master.dbo.sysprocesses(nolock) where dbid = db_id(@db_name) and spid > 50
union
select distinct req_spid from sys.syslockinfo(nolock) where rsc_dbid = db_id(@db_name) and req_spid > 50

/* For SQL 2005/2008 */
/*
select spid from master.dbo.sysprocesses(nolock) where dbid = db_id(@db_name) and spid > 50
union
select distinct request_session_id from sys.dm_tran_locks (nolock) where resource_database_id = db_id(@db_name) and request_session_id > 50
*/

open spid
fetch next from spid
into @spid

while @@fetch_status = 0
begin
exec ('kill ' + @spid)
fetch next from spid into @spid
end

close spid
deallocate spid