Kill all SPID's in a database

One way to kill the the connections in a database is by setting it to 'offline' mode with rollback immediate and then setting it back to 'online' mode. The rollback immediate clause kill all the connections by force.

use master

go

alter database dbname set offline with rollback immediate

go

alter database dbname set online

go

Another way of killing all the connections to a database is by identifying all the SPID's in that database and killing all of them. It can be done using a simple script below. Just use the appropriate database name in place of 'dbname' below.

use master

go

set quoted_identifier off

declare @cmd nvarchar(200)

declare @spid varchar(100)

declare varcursor cursor for

select spid from master..sysprocesses where db_name(dbid)='dbname'

open varcursor

fetch next from varcursor into @spid

while (@@fetch_status=0)

begin

select @cmd= 

"USE [master]

kill "+@spid+"

"

print @cmd

exec sp_executesql @cmd

fetch next from varcursor into @spid

end

close varcursor

deallocate varcursor