How do I Change SQL Database from Single_User to Multi_User Mode

How do I Change SQL Database from Single_User to Multi_User Mode:
Sometimes, we have the need to set the database in Single User Mode to do some upgrades or blocking all the access to the Database.

Set the Database to Single User Mode :

When you to set it back to MULTI_USER , you get the following error.

Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database ‘[DBNAME]’ 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 1
ALTER DATABASE statement failed.

Because the database is in Single User mode and available one connection is already taken.
One Option is to find out which process is using that one connection and you take over that connection.

Get the Process : (SPID)

Leave a Reply

Your email address will not be published. Required fields are marked *

*