How to force SQL Server to drop DB connections

I keep on needing this script so I thought I’d post it here.

Use Master
Go

Declare @dbname sysname
Set @dbname = '<YOUR DATABASE NAME>'

Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)

While @spid Is Not Null
 Begin
 Execute ('Kill ' + @spid)
 Select @spid = min(spid) from master.dbo.sysprocesses
 where dbid = db_id(@dbname) and spid > @spid
 End

Advertisements