Kill All Processes for a Database in SQL Server

To kill all processes for a database, set it to SINGLE_USER with ROLLBACK IMMEDIATE, then back to MULTI_USER. SQL Server disconnects every other session and rolls back its open work. The whole job takes two statements.

Build the Demo

The demo creates a database named KillSessionsDemo with a two-row table. Run it on a test server, never on a database that real users depend on.

IF DB_ID(N'KillSessionsDemo') IS NULL CREATE DATABASE KillSessionsDemo;
GO
USE KillSessionsDemo;
GO
DROP TABLE IF EXISTS dbo.Jobs;
CREATE TABLE dbo.Jobs (JobID int PRIMARY KEY, State nvarchar(20) NOT NULL);
INSERT INTO dbo.Jobs (JobID, State) VALUES (1, N'Queued'), (2, N'Queued');

Now create two more connections. Open two new query windows and run USE KillSessionsDemo; in each. In the first one, run BEGIN TRANSACTION and then an UPDATE on dbo.Jobs. Leave the transaction open. This window plays a stuck job.

Back in your main window, count the user sessions that sit in the database. The view sys.dm_exec_sessions has a database_id column, so no cursor or older system table is needed. Run the count from master, so that your own session isn’t part of the number.

USE master;
GO
SELECT COUNT(*) AS UserSessions
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID(N'KillSessionsDemo') AND is_user_process = 1;

The result is 2 when the two extra windows are open. Without them, it returns 0. The next sections need the two windows open.

Method One: SINGLE_USER With ROLLBACK IMMEDIATE

This is the fastest method. SINGLE_USER allows one connection. ROLLBACK IMMEDIATE tells SQL Server not to wait. It rolls back open transactions and disconnects the sessions at once. Setting the database back to MULTI_USER reopens the door. Run it from the master database, not from inside the target.

USE master;
GO
ALTER DATABASE KillSessionsDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE KillSessionsDemo SET MULTI_USER;

SQL Server reports that nonqualified transactions are being rolled back. Run the count again, and it returns 0. The stuck job’s update never happened, because its transaction was rolled back, and the row still reads Queued. The killed window prints a message that says the session is in the kill state.

There is one weakness. In single-user mode, the database admits one connection. Another session can take that slot before your next statement does. On a busy server, use method three. It also closes the door to regular users, but an administrator login always gets in, so the slot race disappears.

The second statement is the one that reopens the database. If it doesn’t run, because the connection dropped between the two, the database stays in single-user mode. Check user_access_desc in sys.databases, and run SET MULTI_USER again. Don’t use these methods on system databases.

Method Two: Build the KILL Statements

Some tools must keep working, so you don’t always want to change the access mode. The next script builds one KILL statement for every user session in the database and runs them together. The condition on @@SPID keeps your own session out of the list.

DECLARE @kill nvarchar(max) = N'';
SELECT @kill += N'KILL ' + CAST(session_id AS nvarchar(10)) + N'; '
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID(N'KillSessionsDemo') AND session_id <> @@SPID AND is_user_process = 1;
EXEC (@kill);

This method loops over the sessions in a single pass, with no cursor. It has one weakness of its own. It doesn’t close the door, so a session that reconnects right away is back in the database. An application with a retry loop does exactly that.

Method Three: RESTRICTED_USER

RESTRICTED_USER admits only members of db_owner, dbcreator and sysadmin. Regular users get disconnected, and administrators can still work. It’s the polite version of method one, and it fits maintenance work that needs a few people inside.

USE master;
GO
ALTER DATABASE KillSessionsDemo SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
SELECT user_access_desc FROM sys.databases WHERE name = N'KillSessionsDemo';
ALTER DATABASE KillSessionsDemo SET MULTI_USER;

After the last statement, the access mode is MULTI_USER again.

Check who is connected before you kill anything. The view sys.dm_exec_sessions also lists the login, the host and the program of each session. Read that list first, so you know whose work you end.

What the Killed Sessions See

A killed session gets a connection error. The text says that the session is in the kill state, and the application must reconnect. Any work in its open transaction is gone. A large rollback keeps running after the session disappears. The command KILL with the session number and WITH STATUSONLY shows the progress of a rollback.

Is This Ever a Good Idea?

You could argue that you should never kill all processes for a database. A rollback can run for as long as the transaction ran, and the users lose work without a warning. On a production database, that’s a serious step.

The right moments are narrow. A test database that needs a restore. A stress test that left queries behind. A maintenance window where everyone agreed to be disconnected. Before you run it, look at who is connected, and ask them to stop first when you can.

What to Remember

To kill all processes for a database, use SINGLE_USER with ROLLBACK IMMEDIATE when you need the door closed. Use the KILL script when you need the access mode untouched. Use RESTRICTED_USER when administrators must stay inside. All three roll back open work, so count the sessions first and tell the owners.

Remove the demo database when you finish, and close the two extra windows first.

USE master;
GO
ALTER DATABASE KillSessionsDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE KillSessionsDemo;

Killing a session is not housekeeping, it is a decision about someone else’s unfinished work.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.