Kill All Active Connections To A Database

To kill all the connections to your database you can use the ALTER DATABASE command. Sometimes you want to restore the database from a backup but can’t because people are connected. Here is one way which will kick off all the users immediately

TSQLLINE NUMBER OFF | HIDE | SELECT ALL
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE

–do you stuff here

ALTER DATABASE YourDatabase SET MULTI_USER

The code below will wait 60 seconds

TSQLLINE NUMBER OFF | HIDE | SELECT ALL
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK AFTER 60 SECONDS

–do you stuff here

ALTER DATABASE YourDatabase SET MULTI_USER
The reason this is better than looping over sysprocesses or sys.dm_exec_sessions is that nobody will connect while that looping piece of code runs

http://wiki.lessthandot.com/index.php/Kill_All_Active_Connections_To_A_Database

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