Kill All Active Connections To A Database
From Wiki
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
- 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
- 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
Contributed by: --SQLDenis 16:30, 4 June 2008 (GMT)
Part of SQL Server Admin Hacks
