Roger Gullo
asked on
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."
The reason is obvious as there should be only one user connected to the DB
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That was quite simple. I read about DAC but never found the technical bulletin that you provided the link to. Many thanks.
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
USE MASTER;
-- Add users
ALTER DATABASE [my_db] SET MULTI_USER
GO