To kill all connections to a SQL Server database, use the ALTER DATABASE command with ROLLBACK IMMEDIATE, or query sys.dm_exec_sessions to generate individual KILL statements. The fastest production-safe method is a two-line T-SQL script that sets the database to single-user mode with immediate rollback, then returns it to multi-user mode. This terminates all active sessions instantly without restarting SQL Server or taking the instance offline.

Knowing how to kill all database connections is one of those skills that sits quietly in your toolkit until the moment you desperately need it. A restore is blocked. A deployment is stalled. An application is hammering a database with orphaned connections and nothing will proceed until you clear the decks. When that moment arrives, you need a script that works, not a forum thread from 2006 that relies on a cursor and sp_who.

This article gives you exactly that, along with the context to use it safely.

The Fastest Method: ALTER DATABASE with ROLLBACK IMMEDIATE

This is the approach most experienced DBAs reach for first. It's clean, it's fast, and it's documented by Microsoft as the correct way to force exclusive access to a database.

USE master;
GO

ALTER DATABASE [YourDatabaseName]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE [YourDatabaseName]
SET MULTI_USER;

What happens here: the ROLLBACK IMMEDIATE clause tells SQL Server to terminate all active transactions and connections without waiting. Sessions are killed, uncommitted transactions are rolled back, and the database briefly enters single-user mode before you flip it back to multi-user. The whole operation typically completes in under a second for most workloads.

Replace [YourDatabaseName] with your actual database name. Run this from the master database context to avoid being the connection you're trying to kill.

One important caveat: if your own session is the one with an open transaction, you can block yourself. Always run this from a clean session in master with no active transactions against the target database.

A More Targeted Approach: Kill Specific Sessions

Sometimes you don't want a blunt instrument. Maybe you need to kill connections from a specific application, a specific login, or connections that have been idle for more than a defined threshold. In those cases, querying sys.dm_exec_sessions gives you the precision you need.

-- Generate KILL statements for all connections to a specific database
SELECT 'KILL ' + CAST(session_id AS VARCHAR(10)) + ';'
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('YourDatabaseName')
  AND session_id != @@SPID;

This produces a list of KILL statements you can review and execute. The @@SPID exclusion ensures you don't kill your own session.

For a fully automated version that executes the kills directly:

DECLARE @kill VARCHAR(8000) = '';

SELECT @kill = @kill + 'KILL ' + CAST(session_id AS VARCHAR(10)) + '; '
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('YourDatabaseName')
  AND session_id != @@SPID;

EXEC(@kill);

This approach is particularly useful when you're dealing with connection pool exhaustion and need to identify which application or login is responsible before you act. Add a filter on login_name, host_name, or program_name to narrow the scope.

How Many SQL Connections Are Too Many?

SQL Server doesn't impose a hard connection limit by default. The theoretical maximum is 32,767 concurrent connections, but you'll hit memory and CPU constraints long before that number becomes relevant in practice.

In most production environments, warning signs appear well before the connection count becomes critical:

  • OLTP databases typically handle 200-500 concurrent connections comfortably on mid-range hardware.
  • Connection pool exhaustion usually becomes visible at 50-150 connections depending on pool configuration and query duration.
  • Blocking chains compound the problem, because blocked sessions hold connections open while waiting, inflating the count artificially.

If you're regularly seeing connection counts above 300-400 on a single database, that's a signal worth investigating. The issue is rarely the connections themselves. It's usually long-running queries, missing indexes causing table scans that hold locks, or application code that isn't closing connections properly.

Run this to get a current picture:

SELECT
    DB_NAME(database_id) AS DatabaseName,
    COUNT(*) AS ConnectionCount,
    SUM(CASE WHEN status = 'sleeping' THEN 1 ELSE 0 END) AS SleepingConnections,
    SUM(CASE WHEN status = 'running' THEN 1 ELSE 0 END) AS ActiveConnections
FROM sys.dm_exec_sessions
WHERE database_id > 0
GROUP BY database_id
ORDER BY ConnectionCount DESC;

A high ratio of sleeping to active connections often points to a connection pooling misconfiguration on the application side rather than a SQL Server problem.

Why You Might Need to Kill All Connections in an Emergency

Understanding when this operation is appropriate matters as much as knowing how to do it. The most common scenarios where you genuinely need to kill all connections include:

  1. Database restores - SQL Server requires exclusive access to restore a database. Any active connection blocks the operation.
  2. Detaching a database - Same requirement. Active sessions prevent detachment.
  3. Applying schema changes - Some ALTER operations require exclusive locks that existing connections block.
  4. Recovering from a blocking chain - A long-running transaction at the head of a blocking chain can be faster to kill outright than to wait out.
  5. Emergency maintenance - When you're providing urgent SQL Server help and need to get a database into a known state quickly.
  6. Orphaned connection cleanup - Application crashes sometimes leave connections open indefinitely, consuming resources and preventing maintenance.

In genuine emergency DBA situations, the ALTER DATABASE ROLLBACK IMMEDIATE approach is almost always the right first move. It's reversible, it's fast, and it doesn't require restarting the SQL Server service.

What About PostgreSQL and MySQL?

Since these questions come up frequently when people are searching for SQL Server down help across different platforms:

PostgreSQL uses a different approach. To kill all connections to a PostgreSQL database, query pg_stat_activity and use pg_terminate_backend():

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'your_database_name'
  AND pid != pg_backend_pid();

MySQL doesn't have an equivalent single command. You need to identify connections with SHOW PROCESSLIST and issue individual KILL statements, or use a stored procedure to iterate through them. This is one area where SQL Server's ALTER DATABASE approach is genuinely more convenient.

The scripts in this article are specific to SQL Server (2012 and later). Don't apply them to other database platforms.

Before You Kill: What to Check First

Killing connections is a legitimate tool, but it's worth taking 30 seconds to understand what you're terminating. Killing a session mid-transaction rolls back that transaction, which can take time on large transactions and generates log activity.

Before executing, run this to see what's active:

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    s.last_request_start_time,
    t.text AS current_query
FROM sys.dm_exec_sessions s
OUTER APPLY sys.dm_exec_sql_text(
    (SELECT TOP 1 r.sql_handle
     FROM sys.dm_exec_requests r
     WHERE r.session_id = s.session_id)
) t
WHERE s.database_id = DB_ID('YourDatabaseName')
  AND s.session_id != @@SPID
ORDER BY s.last_request_start_time;

This shows you who's connected, what they're running, and how long they've been active. In most emergency scenarios you'll proceed anyway, but knowing what you're terminating is good practice and can inform the post-incident review.

Key Takeaways

  • The fastest way to kill all connections to a SQL Server database is ALTER DATABASE [name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE, followed immediately by SET MULTI_USER.
  • Use sys.dm_exec_sessions filtered by database_id for targeted session management, with @@SPID excluded to protect your own connection.
  • High connection counts are usually a symptom of blocking, long-running queries, or application-side connection pool misconfiguration, not a SQL Server capacity problem.
  • Always run connection-kill scripts from the master database context with no open transactions against the target database.
  • Rolling back large transactions after a kill can take significant time. Check active transaction sizes before proceeding in high-stakes situations.

What to Do Next

If you've landed here because something is broken right now, DBA Services provides emergency SQL Server support for Australian businesses. Our team has handled hundreds of connection and blocking emergencies across SQL Server 2012 through 2022, and we're available when your database can't wait.

If the immediate crisis is resolved, the next step is understanding why it happened. Connection exhaustion, blocking chains, and orphaned sessions are rarely one-off events. They're symptoms of underlying configuration or application issues that will recur.

A DBA Services SQL Server health check examines connection management, wait statistics, blocking history, and session configuration across your environment. In our experience, over 90% of health checks uncover at least one configuration issue contributing to recurring connection problems, whether that's missing connection pool limits, absent query timeouts, or long-running transactions that should have been broken up years ago.

Contact DBA Services to arrange emergency SQL server support or a scheduled health check. We work with IT managers and DBAs across Australia to keep SQL Server environments stable, performant, and properly configured for the workloads they're actually running.