How to Get Exclusive Access to SQL Server Database

By: Editor
March 29, 2010

Introduction

There are scenarios when database administrator needs to disconnect all the connected users from a SQL Server Database to get exclusive access of the Database. The exclusive access is needed before restoring a database, before a database can be detached, to perform critical maintenance tasks, to recover a database which is in Suspect Mode etc. In this article we will take a look at how database administrator can leverage ALTER DATABASE Commands to disconnect users from a database.

Using ALTER DATABASE SET Options

Database administrator can executed ALTER DATABASE command to get exclusive or restricted access of a database.

Get Restricted Access to a SQL Server Database

Database administrator can execute the below mentioned TSQL command to get Restrictive access to a database.

ALTER DATABASE DatabaseName
SET
RESTRICTED_USER WITH ROLLBACK IMMEDIATE
GO

Get Single User Access to a SQL Server Database

Database administrator can execute the below mentioned TSQL command to get a Single User access to a database.

ALTER DATABASE DatabaseName
SET
SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

Give Multi User Access to a SQL Server Database

Database administrator can execute the below mentioned TSQL command to give Multiple User access to a database.

ALTER DATABASE DatabaseName
SET
MULTI_USER
GO

Difference Between SINGLE_USER, RESTRICTED_USER & MULTI_USER Alter Database SET Commands

SINGLE_USER

When SINGLE_USER WITH ROLLBACK IMMEDIATE command is used only one user can connect to the database at a time.

RESTRICTED_USER

When RESTRICTED_USER WITH ROLLBACK IMMEDIATE command is used any number of users who are in DB_OWNER, DB_CREATOR or SYSADMIN roles can connect to the database.

MULTI_USER

When MULTI_USER command is used any number of users who have rights to connect to the database will be able to connect to the database.



Get Exclusive Access to a SQL Server Database

Database administrator can execute the below mentioned TSQL command can be used to get exclusive access to a database.

USE MASTER
GO

/*
All open transactions are rolled back immediately without waiting for them to complete */

ALTER
DATABASE AdventureWorks
SET
SINGLE_USER WITH ROLLBACK IMMEDIATE
GO


/* All open transactions are rolled back after waiting for 60 seconds */


ALTER
DATABASE AdventureWorks
SET
SINGLE_USER WITH ROLLBACK AFTER 60
GO


/* SQL Server will approximately wait for 20 Sec and if open transactions
are not rolled back then statement will fail */

ALTER DATABASE AdventureWorks
SET
SINGLE_USER WITH NO_WAIT
GO

Difference Between ROLLBACK IMMEDIATE, ROLLBACK AFTER INTEGER (SECONDS) & NO_WAIT Alter Database SET Commands

ROLLBACK IMMEDIATE

When ROLLBACK IMMEDIATE SET option is specified then all the open transactions are rolled back immediately without waiting for it to complete.

ROLLBACK AFTER INTEGER (SECONDS)

When ROLLBACK AFTER INTEGER (SECONDS) SET option is specified then all the open transactions are rolled back after waiting for N SECONDS.

NO_WAIT

When NO_WAIT SET option is specified then SQL Server will wait to get the user database in SINGLE_USER mode until all the open transactions have completed. Generally SQL Server will wait for approximately 20 seconds and within this time frame if transactions are not completed then the statement will fail. When this option is used no transactions are rolled back.

Important Note

You will receive the below mentioned error when trying to restore a database.

Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

You may receive the following error message “Cannot detach the database 'AdventureWorks' because it is currently in use” when trying to detach database.

Cannot detach the database 'AdventureWorks' because it is currently in use.
(Microsoft SQL Server, Error: 3703)

In such scenarios database administrator can run the below mentioned TSQL command to get the exclusive access of the database. Once you get the exclusive access of a database then you will be able to restore or attach the database.

ALTER DATABASE AdventureWorks
SET
SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE AdventureWorks
FROM
DISK = 'D:\Backups\AdventureWorks.BAK'
GO

Conclusion

In this article you have seen how easily you can get the exclusive or restricted access of a database using ALTER DATABASE SET commands. As a database administrator you need to be very sure that when Rollback Transaction is issued no critical process is running as it will take long time for the rollback operation to complete.

Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article

Geeks who read this article also read…



Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."