How to Get Exclusive Access to SQL Server Database

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.