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.
- 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
- 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:
- Changing the database property in SQL Server Management Studio (SSMS)
- 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.
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.
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.
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.
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:
|
1 2 3 |
use master go alter database [AdventureWorks2017] set single_user with rollback immediate |
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:
|
1 2 3 |
use master go select name, user_access_desc from sys.databases where name='AdventureWorks2017' |
Usage
Now, let us explore the purpose and usage of the ALTER DATABASE SET SINGLE_USER statement.
- Detach the database
- 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:
|
1 2 3 |
use master go alter database [AdventureWorks2017] set single_user with rollback immediate |
The above command will close all the open connections and set the database in SINGLE_USER. Run the following query to detach the database.
|
1 2 3 |
use master go exec sp_detach_db [AdventureWorks2017] |
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:
|
1 2 3 |
USE [master] go RESTORE DATABASE [AdventureWorks2017] FROM DISK = N'C:\MS_SQL\Backup\adv2017.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5 |
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.
|
1 2 3 |
use master go Alter database [AdventureWorks2017] set single_user with rollback immediate |
The database is in SINGLE_USER mode, try to restore the backup by executing the following command:
|
1 2 3 |
USE [master] go RESTORE DATABASE [AdventureWorks2017] FROM DISK = N'C:\MS_SQL\Backup\adv2017.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5 |
To verify that the backup has been restored successfully or not, run the following query
|
1 2 3 |
use master go select name,user_access_desc from sys.databases where name='AdventureWorks2017' |
As you can see, the database has been restored successfully.
- Note: Before restoring the database, make sure to close the connection which is used to change the access mode of the database
- Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022
Summary
In this article, we learned about the ALTER DATABASE SET SINGLE_USER statement. We have explored the usage of the statement and required permission to execute it. Moreover, I have demonstrated different ways to set the database access mode to SINGLE_USER and the termination clause.
He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com