Set Database from Single User Mode to Multi User

Set database from SINGLE USER mode to MULTI USER

The “user is currently connected to it” might be SQL Server Management Studio window itself. Try selecting the master database and running the ALTER query again.

Change SQL mode from Single User to Multi-User for few or all bases

You could use the undocumented system procedure sp_msforeachdb:

USE master;

EXEC sys.sp_MSforeachdb 'IF ''?'' NOT IN (''master'',''msdb'',''tempdb'',''model'') BEGIN
    ALTER DATABASE ? SET MULTI_USER;
END;';

How to change database from Single user mode to multi user

ALTER DATABASE [MyDB] SET MULTI_USER

If it throws an error like user is already connected to it, select 'master' db in the dropdown and try it that way.

If that doesn't do it, use sp_who to find what spid is accessing the DB and kill it.

Database Stuck in a Single User Mode in SQL Server

By using the the dynamic view "sys.dm_tran_locks" we can find out the
active sessions of the database which is stuck in a single user mode
as follows:

--Query to find the session_id

SELECT request_session_id FROM sys.dm_tran_locks 

WHERE resource_database_id = DB_ID('YourDatabaseName') 

Now kill the session ID found by above query as follows:

-- kill all the processes which are using your database with following query:

    KILL spid

Bring the database into a multi_user mode by using following query:

USE Master
GO
ALTER DATABASE YourDatabaseName SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

Bring the database online.

USE Master
Go
ALTER DATABASE YourDatabaseName SET online
Go

Check the status of the database by using following Query and your Database should be back to normal / multi user mode and online.

select * from sys.databases where name ='YourDatabaseName'

How do I go back to multi_user mode

It's been a while, but I believe the with rollback immediate option is there to say you want this to succeed no matter what. the normal behavior blocks until all running transactions have completed successfully.

Also, setting the database to multi-user mode when it's already multi-user is safe.

However, if you want to be really sure, use a nested try catch block. The first one to handle any error moving to single user mode, and the inner one to handle errors that occur in single-user mode..

see try-catch documentation
and alter database documentation

A couple of final notes for completeness:

1) If the error is severe enough your connection will be closed, and your database will still be in single-user mode.

2) You should have a test instance where you can safely try this stuff out, so that you can learn what's going to happen in production.

Set database to single user mode for database involved in database mirroring session or an availability group

You need to remove the database from the availability group, you can use the following command.

ALTER DATABASE DBName SET HADR OFF;  
GO

Once you have removed the database from the AlwaysOn AG, then you can put the database in single-user mode.

---

Related Topics