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
DBID <> 0
spid <> @@spid

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


July 6, 2009 - Posted by | Scripts, SQL SERVER | ,

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: