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
Feel free to drop a few cents in the
tip jar if this post saved you time and money