To kill multiple processes in SQL Server, you cannot pass a list of IDs directly into a single KILL command. Instead, you have to generate and execute individual KILL <spid> statements.
Here are the three most efficient ways to do this, depending on your needs.
Method 1: The Quick Filter Script (Recommended)
If you want to kill processes tied to a specific database or a specific user, you can run a query that automatically generates the syntax for you.
Run this script to generate the list of kill commands:
SQL
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'KILL ' + CAST(session_id AS VARCHAR(10)) + ';' + CHAR(13)
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('YourDatabaseName') -- Filter by database
AND is_user_process = 1 -- Only target user processes, not system tasks
AND session_id <> @@SPID; -- Do not kill your current connection
-- STEP 1: Preview the commands to make sure they look correct
PRINT @sql;
-- STEP 2: Uncomment the line below when you are ready to execute them all at once
-- EXEC sp_executesql @sql;
Method 2: The Cursor Approach (Fully Automated)
If you want a single script that finds and drops connections instantly without a copy-paste preview step, you can loop through them using a CURSOR. This is highly effective if you are preparing a database for a restore or a drop.
SQL
DECLARE @spid INT
DECLARE @kill_cmd NVARCHAR(50)
DECLARE spid_cursor CURSOR FOR
SELECT session_id
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('YourDatabaseName')
AND is_user_process = 1
AND session_id <> @@SPID;
OPEN spid_cursor
FETCH NEXT FROM spid_cursor INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @kill_cmd = 'KILL ' + CAST(@spid AS VARCHAR(10))
EXEC sp_executesql @kill_cmd
FETCH NEXT FROM spid_cursor INTO @spid
END
CLOSE spid_cursor
DEALLOCATE spid_cursor
Method 3: Kick Everyone Off via ALTER DATABASE
If your goal is to kill every single connection to a specific database right now (for instance, to perform maintenance, rename it, or drop it), the fastest and safest approach doesn't require looking up SPIDs at all. You can forcefully transition the database into SINGLE_USER mode, which automatically terminates all other active connections.
SQL
USE master;
ALTER DATABASE YourDatabaseName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
-- Perform your work here...
-- Don't forget to set it back to multi-user mode when finished!
ALTER DATABASE YourDatabaseName
SET MULTI_USER;
Note on ROLLBACK IMMEDIATE: This option specifies that any open transactions in the database will be instantly rolled back, and their underlying connections severed immediately.
Best Practices & Precautions
Check Status: If you kill a long-running write transaction (like a massive data import or index rebuild), SQL Server must roll back the changes to ensure data integrity. If you check sys.dm_exec_requests, you might see the status as KILLED/ROLLBACK. Do not restart the SQL service to speed this up, as the rollback will simply resume upon startup and often take longer.
Avoid System SPIDs: Always include is_user_process = 1 or filter out session_id <= 50 to avoid accidentally targeting background SQL Server engine processes.
Cheers
Samitha