How to Set Database into Single User Mode

Today when I ran DBCC CHECKDB on my development server, I found errors on one database. When I tried to repair it, there is a message “Repair statement not processed. Database needs to be in single user mode.“

Single-user mode specifies that only one user at a time can access the database and is generally used for maintenance actions.

To change a database into single user mode, we can use sp_dboption. But since this feature will be removed in future version after SQL Server 2000, we use ALTER DATABASE instead of.

Using T-SQL

Set to single mode: ALTER DATABASE [database name] SET SINGLE_USER; Set back into multi user mode: ALTER DATABASE [database name] SET MULTI_USER;

MASTER, MSDB and TEMPDB cannot be set into single user mode. For this you need to start the SQL SERVER into single user mode.

If the command ALTER DATABASE is failed, may be there are another sessions use this database. Use SP_WHO2 without active parameter to find out. Kill related spid that connecting to the database. If using SQL Server 2000, simple way is using EM, right click on the database and choose detach database. On detach database window just clear the connections than click cancel button (don’t detach the database). Close the EM and try to run the ALTER DATABASE again.

Using SQL Server Management Studio
1. Right click the related database then click properties.
2. Choose Option page
3. From Restrict Access option, select Single
4. Click OK and Open Connections dialog will appear. Click Yes if you want to close all connection connected to the database and change the database on single user mode.

Using Enterprise Manager
1. Right click the related database then click properties.
2. Choose Option page.
3. Tick Restrict Access and select Single User.
4. Click OK. If other users connect to the database, this process will be failed. You have to clear the connection manually and try again.

You can use SQL Server Management Studio to connect to database on SQL Server 2000. Since SQL Server Management Studio is able close all connection when we try to change database to single user mode, it is more simple than we use Enterprise Manager.