4526.aspx

How to kill open connections on SQL Server

The stored procedure below, from Michele, kills all open connections to the DB you specify. It is a great time saver when you have to take a busy db offline for maintenance.

CREATE PROCEDURE usp_DBKillConn
@DBname sysname = NULL
AS
create table #SpidDB
(
   spid  smallint
)
declare @curSpid smallint
declare @cmd varchar(64)
INSERT INTO #SpidDB(spid) SELECT spid FROM master.dbo.sysprocesses WHERE dbid = DB_ID(@DBname)

SELECT @curSpid = min(spid) from #SpidDB
while @curSpid IS NOT NULL
begin
   PRINT @curSpid
   SET @cmd = 'KILL ' + convert(varchar(6),@CurSpid)   /*KILL @curSpid*/
   EXEC sp_sqlexec @cmd
   SELECT @curSpid = min(spid) from #SpidDB where spid > @curSpid
end
Print @DBname
GO