SQL script to kill all processes on a specific database

Michael Roma

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;
go



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.