Kill all user processes for a database

 

Safely Terminating Connections in SQL Server (For Test/Dev Use Only)

When working in development or testing environments, you might find it necessary to forcibly disconnect all active sessions from a particular database — for example, before running maintenance tasks or restoring a backup. However, use this procedure with caution and never run it in production environments unless you're absolutely sure of the consequences.

T-SQL Stored Procedure to Kill All Active Sessions on a Database

Below is a T-SQL script that finds and terminates all active connections to a specific database, excluding your own session. This script uses a cursor to loop through the session IDs (SPID) connected to the target database and executes the KILL command for each.

DECLARE @spid INT;
DECLARE @sqlString NVARCHAR(100);

DECLARE conn_cursor CURSOR FOR
    SELECT [SPID] 
    FROM [master].[dbo].sysprocesses
    WHERE [DbId] = DB_ID('DBName') AND [SPID] <> @SPID;

OPEN conn_cursor;
FETCH NEXT FROM conn_cursor INTO @spid;

WHILE @FETCH_STATUS = 0
BEGIN
    SET @sqlString = 'KILL ' + CAST(@spid AS NVARCHAR(10));
    PRINT @sqlString;
    EXECUTE sp_executesql @sqlString;
    FETCH NEXT FROM conn_cursor INTO @spid;
END;

CLOSE conn_cursor;
DEALLOCATE conn_cursor;

Notes

• Replace 'DBName' with the name of your target database.
• This approach uses the legacy sysprocesses system table, which is still available but deprecated. For newer SQL Server versions, consider using the sys.dm_exec_sessions or sys.dm_exec_requests views for more modern alternatives.
• Always make sure you're targeting the correct environment to avoid unintentional disruptions.