SQL script to kill all processes on a specific database

Michael Roma on Nov 20, 2019

This post shows a SQL script that can be used to kill all processes that are connected to a database. This is useful when you need to take the database down for maintenance.

The basis of this script uses the following query to find all the processes ids currently connected:

select spid FROM master..sysprocesses  WHERE dbid = db_id(‘MyDatabaseName’)

From this we can build a series of commands that kill each process:

use master;

DECLARE @s varchar(8000) = “; SELECT @s = @s + ‘kill ’ + CONVERT(varchar(5), spid) + ‘;’ FROM master..sysprocesses WHERE dbid = db_id(‘MyDatabaseName’); exec(@s) go

Also, make sure you are not currently connected to the database. You won’t be able to kill your own process. I usually switch to the master database.