I have finally perfected a nice script for killing connections to databases. Although basic, I found many of the ones I google’d, had one problem or another that stopped them working straight away, which just wastes time. This one works! I have just used it.
Just replace the database name and it’s good. It is easy to alter it to work across a list of databases too.
-- ============================================= -- Author: Nicholas Rogoff -- Create date: 04/03/2010 -- Description: Script that Kills all connections to a database -- except the current one -- ============================================= SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO DECLARE @execSql NVARCHAR(1000) DECLARE @databaseName VARCHAR(100) DECLARE @NoKilled INT -- *** CHANGE THE NAME OF THE DATABASE *** --- SET @databaseName = '#### Replace this with a database name here ####' PRINT 'START: Killing active connections to the "' + @databaseName + '" database' -- Count Connections select @NoKilled = COUNT(*) from master.dbo.sysprocesses where db_name(dbid) = @databaseName and DBID <> 0 and spid <> @@spid -- Create the sql to kill the active database connections set @execSql = '' select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' ' from master.dbo.sysprocesses where db_name(dbid) = @databaseName and DBID <> 0 and spid <> @@spid exec (@execSql) PRINT 'END: Killed "' + CAST(@NoKilled AS VARCHAR(4)) + '" active connections to the "' + @databaseName + '" database' GO