SQL Server 2012 Error: Msg 924 Database is already open and can only have one user at a time.

During the course of executing a restore project command in SQL Server Management Studio, I received the error "Exclusive access could not be obtained because the database is in use". I then went to the database properties and switched it to Single User mode. Since doing that, I am unable to switch it back to "multi-user mode". I get the error "Msg 924 Database is already open and can only have one user at a time."
Roger GulloPrincipalAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Execute the following code. (change [my_db] to your dbname)

SSMS in general uses several connections to the database behind the scenes.

You will need to kill these connections before changing the access mode.

First, make sure the object explorer is pointed to a system database like master.

Second, execute a sp_who2 and find all the connections to database 'my_db'. Kill all the connections by doing KILL { session id } where session id is the SPID listed by sp_who2.

Third, open a new query window.

Execute the following code.

-- Start in master

-- Add users
HuaMin ChenProblem resolverCommented:
The reason is obvious as there should be only one user connected to the DB
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can't restore a database while there are users connected to it and that's why you got the 1st message:  "Exclusive access could not be obtained because the database is in use".

By now you should know that you took the wrong option by setting it to single user mode since that only let a single user to connect to the database and someone already took that connection and that's why you got the 2nd message: "Msg 924 Database is already open and can only have one user at a time."

The best option that you have is to connect to the database by using a Dedicated Administrator Connection since that connection is possible even the database is set to Single User Mode and then you can put back the database to Multi User mode.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roger GulloPrincipalAuthor Commented:
That was quite simple. I read about DAC but never found the technical bulletin that you provided the link to. Many thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.