Kill ALL Connections to a Database

As a SQL Server DBA there will be time when you want to kill all the connections to database which can be for restore or to perform other database tasks. As a DBA you can put the database in single mode or dbo use only, but most of the time you are performing the task as automated process using scripts. You can kill all connections to SQL Server database using the following script, make sure you replace the database name by setting @DBName variable value.

SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr =

Set @DBName = ‘DATABASE_NAME’
IF db_id(@DBName) < 4
BEGIN
PRINT ‘Connections to system databases cannot be killed’
RETURN
END
SELECT @spidstr=coalesce(@spidstr,‘,’ )+‘kill ‘+convert(varchar, spid)+ ‘;’
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END

The above script can be used in SQL Query Analyzer or in SQL Server Agent Job.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: