The DBA Blog by Tomer Lev

SQL information, scripts, tricks and more…

Kill active database connections

— Create the sql to kill the active database connections
declare @execSql varchar(1000), @databaseName varchar(100)
— Set the database name for which to kill the connections
set @databaseName = ‘your db name’

set @execSql = ”
select  @execSql = @execSql + ‘kill ‘ + convert(char(10), spid) + ‘ ‘
from    master.dbo.sysprocesses
where   db_name(dbid) = @databaseName
and
DBID <> 0
and
spid <> @@spid
exec(@execSql

or the easy way is:

-alter database dbName set single_user with rollback immediate

and after you want to bring it back to normal status

-alter database dbName set multi_user with rollback immediate

Advertisements

July 6, 2009 Posted by | Scripts, SQL SERVER | , | Leave a comment