Kill All Active Connections To A Database

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

Jump to: navigation , search

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

 

tsql Line number Off | Hide | Select all

1. ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
2.  
3. --do you stuff here
4.  
5. ALTER DATABASE YourDatabase SET MULTI_USER

The code below will wait 60 seconds

tsql Line number Off | Hide | Select all

1. ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK AFTER 60 SECONDS
2.  
3. --do you stuff here
4.  
5. 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