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.