Nisarg Upadhyay
set database to single user mode

ALTER DATABASE SET SINGLE_USER statement in SQL Server

June 7, 2021 by

In this article, we are going to explore the purpose of the ALTER DATABASE SET SINGLE_USER statement. The ALTER DATABASE SET SINGLE_USER is used to put the database in single-user mode. When any database is in single-user mode, the new user cannot connect to the database. However, the users that are already connected to the database do not get disconnected.

When you are overwriting a database using a backup or trying to detach any database, you might want to change the user access mode to SINGLE_USER to make sure that new users can not connect to the database. When you run the ALTER DATABASE SET SINGLE_USER statement, it does not complete because the users are still connected to it.

To override this behavior, we can use any of the following termination options.

  1. WITH ROLLBACK IMMEDIATE: This option will rollback all the open transactions. This option does not wait for the transaction to complete. When you are rolling back a long-running transaction, the command takes more time to complete, so make sure you check all long-running transactions before executing the command
  2. WITH NO_WAIT: This option does not rollback any transaction but, it waits till all transaction completes. It waits for a specified time, and if the transaction does not complete, the ALTER DATABASE statement fails. This approach is considered the cleanest method to change the access mode because it does not roll back any transaction

Permission

To execute the ALTER DATABASE SET SINGLE_USER statement requires ALTER DATABASE permission.

Different methods to access the mode of the database to a SINGLE_USER

We change the access mode of the database to a single user by using any of the following methods:

  1. Changing the database property in SQL Server Management Studio (SSMS)
  2. T-SQL ALTER DATABASE statement

Change Database Property in SQL Server Management Studio (SSMS)

To change the database mode using SSMS, open SQL Server Management Studio Connect to the database engine Expand Databases Right-click on AdventureWorks2017.

Open database property

In the database properties dialog box, click on Options. Click on Restrict Access drop-down box and select SINGLE_USER. Click OK to save the configuration.

set database to single user mode

You will receive a message stating that we must close all open connections before changing the access mode. Click on OK to close all active user connections and change the access mode.

Error: we must close all open connections before changing the access mode

The access mode of the AdventureWorks2017 database is changed to SINGLE_USER. You can see the (Single-User) is specified with the database name in SQL Server Management studio.

Database is in SINGLE_USER mode

T-SQL ALTER DATABASE Statement

We can use ALTER DATABASE SET SINGLE_USER statement to change the access mode of the user database to SINGLE_USER. To change the access mode of the AdventureWorks2017. Before changing the access mode of the database, we must close the open connections. To do that, we must use the ROLLBACK IMMEDIATE termination clause. The ALTER DATABASE command must be written as follows:

You will receive the below message.

Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

The access mode of the AdventureWorks2017 database will be changed to SINGLE_USER. To verify, run the below query:

ALTER DATABASE SET SINGLE USER command executed successfully

Usage

Now, let us explore the purpose and usage of the ALTER DATABASE SET SINGLE_USER statement.

  1. Detach the database
  2. RESTORE backup on the existing database

Detach the database

When we are detaching a user database, we must close all open connections. Before detaching a database in a production environment, the DBAs send the email to notify the users to disconnect from the application. Sometimes, a part of the application or a module of an application is also connected to the databases. In such cases, the detach command fails because the application is connected to the database. In this case, we must forcefully close the connection and set the access mode to SINGLE_USER to detach the database. To do that, we must use WITH ROLLBACK IMMEDIATE clause to forcefully disconnect all users and open connections. The ALTER DATABASE command is written as follows:

The above command will close all the open connections and set the database in SINGLE_USER. Run the following query to detach the database.

The database will be detached successfully.

RESTORE backup on the existing database

We, as database administrators, need to refresh a development or test databases. We can refresh process a database using detach and attach method or restoring the backup on the existing database by overwriting it. While overwriting the existing database, we might encounter an error: For demonstration, I am overwriting the AdventureWorks2017 database using the backup which I have taken. To restore the backup, run the following query:

You might encounter the following error:

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

To rectify this error, we must close all open connections made to the AdventureWorks2017 database and change the access mode of the database to SINGLE_USER. To do that, run the below query.

The database is in SINGLE_USER mode, try to restore the backup by executing the following command:

To verify that the backup has been restored successfully or not, run the following query

Database has been restored.

As you can see, the database has been restored successfully.

6,493 Views