SQL Server: KILL Processes using a database

If you need to do some operations on a database, sometimes you’re required to be the only user. Typically you will execute this procedure:

exec sp_who

Then you’ll get the spid‘s of each one related to your database and KILL them. The problem is there’s no ability to put a WHERE clause with sp_who.

use master;
select 'kill ', spid from sysprocesses p
	inner join sysdatabases d
	on p.dbid = d.dbid
WHERE d.name = 'DBNAME'

Say spid’s 4, 8, 15, 16, 23, and 42 are using the database ‘DBNAME’. Now you can just copy/paste your results window as a set of new queries:

kill 4
kill 8
kill 15
kill 16
kill 23
kill 42

Comments are closed.

%d bloggers like this: