How to exit from single-user mode in SQL Server database?

Exiting from single-user mode in SQL Server involves resetting the database to multi-user mode. Single-user mode is typically used for maintenance tasks, so it’s important to switch back to multi-user mode once those tasks are completed. Here are two common methods to exit single-user mode:

1. Using SQL Server Management Studio (SSMS)

1. Open SQL Server Management Studio (SSMS).
2. Connect to your SQL Server instance.
3. Open a New Query window pointing to the system database master.
4. Execute the command sp_who2 to identify the connection to the database that is currently in single-user mode.
   
5. Kill the connection using the command KILL { session_id }. session_id is the SPID listed in the sp_who2.
6. Now, From the Object Explorer, expand the Databases node to locate and select the database that is currently in single-user mode.
7. Right-click on the selected database, and choose Properties.
8. In the Database Properties dialog, go to the Options page.
9. In the Restrict Access section, change the Restrict Access option from SINGLE_USER to MULTI_USER. This will switch the database back to multi-user mode.
10. Click the OK button to save the changes.

Note

In the above steps, if you fail to terminate the active connection to the single-user database, you will encounter an error when attempting to change the database properties, as shown in this screenshot.

2. Using Transact-SQL (T-SQL) Query

You can also use T-SQL to change the database from single-user mode to multi-user mode. Here’s how:

-- Connect to the SQL Server instance
USE master;
GO

-- Allow multiple users to access the database
ALTER DATABASE YourDatabaseName
SET MULTI_USER;
GO

Replace YourDatabaseName with the name of the database that you want to switch to multi-user mode.

Note

If there are active connections to the single-user database, you will encounter an error when attempting to change the database properties.

Msg 5064, Level 16, State 1, Line 6
Changes to the state or options of database ‘YourDatabaseName’ cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 6
ALTER DATABASE statement failed.

To avoid the error:

1. Execute the command sp_who2 to identify the active connection to the database that is currently in single-user mode.
2. Kill the connection using the command KILL { session_id }. session_id is the SPID listed in the sp_who2.
3. Now run the above T-SQL command to exit from single-user mode.

Important Note

Be cautious when switching a database from single-user mode to multi-user mode, especially in a production environment. Ensure that no critical maintenance or repair operations are currently running on the database.

Reference

• Read more about ALTER DATABASE at Microsoft Docs.