Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

Custom disconnection techniques

In scenarios where you require a controlled disconnection or prefer granular oversight, consider using the following methods:

1. Killing connections with dynamic SQL

Utilize sp_who2, which helps identify current connections, and use the KILL command to terminate them:

DECLARE @kill varchar(8000) = '';  
SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(5), session_id) + ';' 
-- "Terminate with extreme prejudice"
FROM sys.dm_exec_sessions
WHERE database_id  = db_id('YourDatabase')

EXEC(@kill);


Set your appropriate database name before running. Note that this method is compatible with all SQL Server versions from 2012 and onwards.

2. Force disconnections on older SQL Server versions

For SQL Server versions prior to 2012, you can rely on master..sysprocesses to isolate and kill active processes:

USE master;
DECLARE @kill varchar(8000) = ''; 
SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(5), spid) + ';'
-- "You have no power here, SPIDer"
FROM master..sysprocesses 
WHERE dbid = db_id('YourDatabase') AND spid > 50

EXEC(@kill);


This script will terminate processes associated with your specified database.

3. Navigating automated deployments

If your steps are part of an automated deployment script, it might include one stage where the database needs to be dropped and recreated. During this process, remember to halt any active services or applications that may automatically connect to the online database.

Best scenarios for each method

There is a suitable method for every scenario. Here’s when each strategy works best:

For standard maintenance

The initial fast answer scripts will typically suffice in scenarios where you need to quickly bring the database offline for regular maintenance operations.

During deployment sequences

In deployment scripts, and more specifically, when recreating a database, you'll first need to terminate all existing connections, set the database to SINGLE_USER, or make it OFFLINE, before proceeding with your tasks

To minimize disruptions

If you wish to pull the plug and yet maintain decorum, use the dynamic SQL KILL scripts. They offer more fine-grained control, enabling you to preserve important service sessions.

Dealing with common issues

Tricky situations may still arise, no matter how solid your plan:

Right of Access

Someone else might beat you to exclusive access under SINGLE_USER configuration. Reclaim your right by serving a KILL command with their SPID.

Automatic reconnections

Jobs that reconnect spontaneously can be halted or disabled temporarily, allowing your kill scripts to execute successfully and maintain single user access.

Recurring scenarios

Save time and reduce potential error by automating the disconnection procedure in commonly recurring scenarios.

Overcoming Typical Challenges

Despite your plans, there may be times when the scripts do not behave as expected. Here's how to handle those situations:

Access Rescue Operation

If SINGLE_USER mode gets hijacked by an unknown session, use sp_who2 to identify them, then execute a KILL command to rescue your database.

Transactional Integrity

Apply the WITH ROLLBACK IMMEDIATE clause to ensure any active transactions maintain data integrity when you need to abruptly disconnect users.

Autostart Services

Autostart services or retry scripts can meddle. Tackle them either by halting the service or the script momentarily while you run your kill scripts, or tweak the default database to divert the connections momentarily.