How To Kill All Processes For A Database In SQL Server

I was working on my client’s system whose entire instance CPU was eaten up by the SQL Server. While checking many options and backend processes, we got to know that some random processes were getting generated from a database. We were stressed to kill the sessions one by one. We followed the below options to kill all the processes in a database.

There are two methods to kill all processes generated from a database.

Option 1: Kill all the processes by changing the database to single-user mode

Here is the script to change the database from multi-user to single-user

ALTER DATABASE [DBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE [DBName] SET MULTI_USER;

Option 2: Kill all the processes by a looping script

SET NOCOUNT ON
Declare
@cnt int ,
@i int=1,
@dbname varchar(500),
@cmd nvarchar(max),
@spid nvarchar(max)
drop table if exists #TmpWho
CREATE TABLE #TmpWho
(id int identity(1,1) not null, spid INT, ecid INT, status VARCHAR(100), loginame VARCHAR(100),
hostname VARCHAR(150), blk INT, dbname VARCHAR(150), cmd VARCHAR(100),request_id int)
INSERT INTO #TmpWho
EXEC sp_who
set @cnt=@@rowcount
While (@i<=@cnt)
begin
select @spid=spid from #TmpWho
where id=@i --and @dbname= 'yourdbname'
set @cmd='KILL ' + @spid + ''
print (@cmd)
set @i=@i+1
end

I usually prefer option 1 over option 2 because in most cases killing processes one by one is going to take quite some time. Additionally, option 2 will work slowly in case we have hundreds of threads to kill.

Caution: Be very cautious while executing both the above options to kill the processes in a production environment. Execute the above scripts under a DBA's supervision.

You can kill sessions using Activity Monitor too.