SQL script to kill all connections to a SQL Server database

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

The free online div table generator allows you to create nice grids for your websites. Please subsribe for a htmlg membership to stop adding promotional messages to the edited documents.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.