kill many process in sql server

 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