Backup & Restore

Kill All Active Sessions In Database

I usually write this ahead of any database restores I might want to run.

use master;
set nocount on
declare @my_database_name varchar(255) declare @kill_all_connections_to_database varchar(max)
set @my_database_name = ‘MyDatabaseName’ — put your database name in here. set @kill_all_connections_to_database = ”
select @kill_all_connections_to_database = @kill_all_connections_to_database + ‘kill ”’ + cast(spid as varchar(4)) + ‘; ‘ + char(10)
from sysprocesses where db_name(dbid) = @my_database_name
exec (@kill_all_connections_to_database)
go

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s