November 10, 2010

Drop all Connections to SQL Server Database

Here’s a simple script to drop all active connections to the SQL Server Database. You usually drop connections when you are planning to take the database offline or need to immediately deal with a maintenance issue.

If you want to view the active connections on a database, read my post View active connections for each Database in SQL Server

Drop all connections and allow database access to few users

ALTER DATABASE AdventureWorks SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE


Note 1: The RESTRICTED_USER option allows the database to be accessed by only members of the db_owner, dbcreator or sysadmin roles

Note 2: The ROLLBACK IMMEDIATE option forcefully disconnects all users without allowing any work in progress to complete. If you do not plan to disconnect the users immediately, but disconnect say after 1 minute, use this option

ALTER DATABASE AdventureWorks
SET RESTRICTED_USER WITH ROLLBACK AFTER 60 SECONDS

Drop all connections and allow database access to only one user

ALTER DATABASE AdventureWorks
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

The SINGLE_USER option allows the database to be accessed only by one user, who can be anyone.

Return Access to the Database as it was earlier

ALTER DATABASE AdventureWorks SET MULTI_USER


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

2 comments:

Anonymous said...

thanks and can you also post about different ways to take db offline?

Anonymous said...

This will surprise you around 3 AM someday. It is not foolproof.