Kill All Connections to a SQL Server Database (Safely)

The one-liner is ALTER DATABASE MyDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE, and it works. What the one-liner does not tell you is that it aborts every in-flight transaction, that the resulting rollback can take longer than whatever you were waiting for, and that another session can steal the single-user slot before you use it.

You are almost certainly here because a restore or a drop failed with a message about the database being in use. That message is correct and the standard fix is well known. It is worth understanding what the fix actually does before running it on anything you care about, because there is a version of this that turns a two-minute maintenance window into a forty-minute one.

Table of contents

• Find out who is connected first
• The standard approach and what it costs
• Gentler options
• Killing individual sessions instead
• The single-user race, and how to avoid it
• Do it in the right order
• How this fits the rest of the stack
• FAQ

Find out who is connected first

Thirty seconds of looking often changes what you do next. Sometimes it is one forgotten SSMS window; sometimes it is the entire application still serving traffic.

SELECT s.session_id,
       s.login_name,
       s.host_name,
       s.program_name,
       s.status,
       s.last_request_end_time,
       r.command,
       r.percent_complete
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r
       ON r.session_id = s.session_id
WHERE s.database_id = DB_ID('MyDatabase')
  AND s.session_id <> @@SPID;

The program_name column is the useful one. It will say Microsoft SQL Server Management Studio, or name your application’s connection string, or reveal a reporting tool nobody remembered.

If it is one SSMS session belonging to you in another window, close that window and the problem is solved with no risk at all. This is a surprisingly large share of cases.

If it is the application, stopping the application or draining its connection pool is the correct answer rather than killing connections underneath it. Killing a connection mid-write means the application sees an error, and depending on how it handles that, retries into a database you are about to take offline.

Also check for anything with percent_complete set — a running backup, restore, or index rebuild. Killing one of those has consequences of its own.

The standard approach and what it costs

SINGLE_USER restricts the database to one connection. WITH ROLLBACK IMMEDIATE tells SQL Server to terminate the existing ones straight away rather than waiting for them to finish.

ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- do the restore, drop, or maintenance here

ALTER DATABASE MyDatabase SET MULTI_USER;

Immediate refers to the decision, not the duration. Every open transaction must be rolled back, and rolling back a transaction that has done a great deal of work takes roughly as long as the work took. A bulk update forty minutes into a fifty-minute run will spend a comparable time rolling back, and the ALTER DATABASE statement waits for it.

This is the trap. It looks like a fast command and it can block for a long time on exactly the occasion you least want it to.

Check for expensive open transactions before running it:

SELECT t.session_id,
       t.transaction_id,
       tat.transaction_begin_time,
       DATEDIFF(SECOND, tat.transaction_begin_time, GETDATE()) AS age_seconds
FROM sys.dm_tran_session_transactions AS t
JOIN sys.dm_tran_active_transactions AS tat
  ON tat.transaction_id = t.transaction_id
ORDER BY tat.transaction_begin_time;

A transaction that has been open for a long time is a warning. Consider waiting for it, or find out what it is doing before you abort it.

Gentler options

ROLLBACK IMMEDIATE is not the only choice, and it should not be the reflex on a production system.

Give sessions a grace period to finish:

ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK AFTER 60 SECONDS;

Sessions get 60 seconds to complete, after which anything still running is terminated. Short transactions finish cleanly, long ones are aborted anyway. This is usually the better default on a live system.

Or refuse to disturb anyone at all:

ALTER DATABASE MyDatabase SET SINGLE_USER WITH NO_WAIT;

This fails immediately if the change cannot be made without terminating something. Useful in a script where you would rather abort the maintenance and retry later than cause an incident.

There is also RESTRICTED_USER, which allows members of db_owner, dbcreator, and sysadmin to remain connected while excluding everyone else. That is what you want for maintenance you need several administrators to work on together, since SINGLE_USER genuinely means one connection and locks out your colleague as effectively as it locks out the application.

Killing individual sessions instead

When only a handful of sessions are in the way, terminating them specifically is more surgical than taking the whole database single-user.

KILL 58;

-- Watch a rollback that is taking its time
KILL 58 WITH STATUSONLY;

STATUSONLY reports rollback progress as a percentage and an estimated completion time. When a KILL appears to hang, this tells you whether it is progressing or genuinely stuck, which is the difference between waiting and escalating.

To generate the statements rather than typing session ids by hand:

SELECT 'KILL ' + CAST(session_id AS VARCHAR(10)) + ';'
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('MyDatabase')
  AND session_id <> @@SPID;

Review the output before running it. Generating and immediately executing a list of KILL statements is how you terminate a backup that was ninety percent complete.

Note that some sessions cannot be killed — those in certain rollback states, or system sessions. If a KILL does nothing, STATUSONLY will usually explain why.

The single-user race, and how to avoid it

A detail that produces one of the more baffling failures in this area.

Once the database is in SINGLE_USER mode, exactly one connection may use it, and SQL Server does not reserve that slot for you. If an application with automatic reconnection is still running, it can grab the single available connection in the instant between your ALTER statement completing and your next statement executing.

You then find yourself locked out of a database you just set to single-user, unable to run the restore, and unable to set it back to MULTI_USER without killing the connection that took your place.

Two defences. Stop the application, or block its access, before starting — this is the reliable one. And run the whole sequence as a single batch so there is no gap:

USE master;
GO
ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE MyDatabase FROM DISK = 'D:\Backups\MyDatabase.bak' WITH REPLACE;
ALTER DATABASE MyDatabase SET MULTI_USER;
GO

Run this from the master database. Running it while your own session is connected to the target database means your session is one of the ones being terminated, which is an efficient way to disconnect yourself.

Do it in the right order

The whole procedure, in the order that avoids the problems above.

1. Stop the application, or drain its connection pool. This removes the reconnection race and prevents the application from erroring in front of users.
2. Look at who is still connected and what they are doing. Check for long-running transactions and anything with percent_complete.
3. Take a backup if you are about to do something destructive, and confirm it completed.
4. Set SINGLE_USER, preferably with ROLLBACK AFTER a grace period rather than IMMEDIATE, from a session connected to master.
5. Perform the restore, drop, or maintenance.
6. Set MULTI_USER.
7. Start the application and confirm it connects.

Step one is the one most often skipped and the one that removes most of the risk. Everything else in this article is a workaround for not having done it.

And a general note: if you find yourself needing this regularly, the underlying issue is usually a maintenance process that fights the application rather than being coordinated with it. A brief, planned window where the application is stopped is calmer than repeatedly terminating connections under a running system.

How this fits the rest of the stack

SINGLE_USER WITH ROLLBACK IMMEDIATE is the right tool and a blunt one. Look at who is connected first, prefer a grace period on anything live, run the sequence as one batch from master, and stop the application rather than fighting its reconnection logic. If you are working with MySQL or Postgres rather than SQL Server, managed instances on RunxBuild handle backups and connection limits at the platform level, which removes a class of maintenance where this pattern is needed. The RunxBuild hosting calculator shows the database alongside the service that connects to it.

Useful related references:

• Port 5432: PostgreSQL Connections, and the Two Files That Decide Everything
• Blocking SSH on a Cisco Router: ACLs, VTY Lines, and the Lockout Trap
• Kill a Process in Linux: Why kill -9 Should Be Your Last Move
• Databases on RunxBuild

FAQ

How do I kill all connections to a SQL Server database?

ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE, then perform your maintenance and set it back to MULTI_USER. Run it from a session connected to master, not to the target database, or you will terminate your own connection along with everyone else’s.

Is ROLLBACK IMMEDIATE safe?

It is safe for data integrity — transactions roll back rather than partially applying. It is not safe for your maintenance window, because rolling back a long transaction takes roughly as long as the work took, and your ALTER statement waits for it. Check for old open transactions first, and prefer ROLLBACK AFTER a grace period on live systems.

Why can I not connect after setting SINGLE_USER?

Because another session took the single available slot before you did, most likely an application with automatic reconnection. SQL Server does not reserve the slot for whoever issued the ALTER. Stop the application first, and run the whole sequence as one batch so there is no gap for something else to connect.

What is the difference between SINGLE_USER and RESTRICTED_USER?

SINGLE_USER allows exactly one connection, which locks out your colleagues as well as the application. RESTRICTED_USER allows members of db_owner, dbcreator, and sysadmin while excluding everyone else, which is what you want for maintenance involving more than one administrator.

How do I check whether a KILL is still rolling back?

Run KILL with STATUSONLY against the session id. It reports the rollback percentage and an estimated completion time, which distinguishes a slow rollback that is progressing from one that is genuinely stuck. Without it, a hanging KILL gives no indication of which situation you are in.