SQL Server Script For Killing all other connections

In SQL Server there comes a time when you need to disconnect all other connections to the database. An example is when you wish detach the database for backup or other purposes. This article will show you the script for doing this. Simply follow the script changing database_nameto the name of the database that you wish to kill the connections to.

DECLARE @dbid tinyint
DECLARE @spid smallint
DECLARE @exec_str varchar(10)

-- Get the ID of the Database you wish to kill the connections of
SET @dbid = DB_ID('database_name')

-- Set a var to the first process ID connecting to that database
SET @spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid = @dbid)

WHILE @spid IS NOT NULL
BEGIN
IF @spid <> @@SPID -- Make sure you don't drop your own connection
BEGIN
SET @exec_str = 'KILL '+LTRIM(STR(@spid))
EXEC(@exec_str) -- Kill the connection
END
--Get next SPID
SET @spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid = @dbid AND spid > @spid)
END

---