Kill all connections to SQL Server database using a script, kill sql server connections

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.

Note: The job can not kill its own connection, if you are using SQL Sever Query analyzer to kill connections to the database then try to issue a use statement against any other database on top of the query, else you will receive an error.

USE master
GO

Its always a good idea to notify users before killing the connection to database using a script, by restarting the computer or any other way.

DONT KILL CONNECTIONS LIKE THIS :
Wrong way of killing the connections to database is by taking out the network wire, or by disabling network devices.