Login or Sign Up to become a member!

EXPERTS, INFORMATION, IDEAS & KNOWLEDGE

Social bookmarker Add this

Kill All Active Connections To A Database

From Wiki

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


  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

  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


Contributed by: --SQLDenis 16:30, 4 June 2008 (GMT)

Part of SQL Server Admin Hacks

333 Rating: 5.0/5 (1 vote cast)