October 13, 2011 at 8:13 am
I changed a database to Single User mode in SSMS and now I cannot change it back to Multi User mode. When I click to view the Properties of the database to change it, I get the message: "Database is already open and can only have one user at a time (Microsoft SQL Server, Error: 924)"
How can I get it switched back to Multi User mode?
Thanks!
October 13, 2011 at 8:27 am
Can you access it via a connection window? (Where you type queries and all that.)
If so:
alter database MyDatabaseName set multi_user;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 13, 2011 at 9:08 am
You can check for open connections to the database through Activity Monitor or using sp_who2 through a query window.
You can kill open connections to the database using the kill command. Once there are no connections to the database you should be able to make the change.
It is possible that another connection is using the database using 3 part naming through another database so bear that in mind
October 13, 2011 at 9:29 am
Thanks MysteryJimbo! That fixed my problem!!!!!
December 4, 2012 at 3:26 am
I have the same problem and mystery jumbo may be right but i can not even check sp_who2 to find who has a open connection, what to do?
December 4, 2012 at 4:50 am
Run this in SSMS
USE yourDB
GO 1000
When the command succeeeds you will be able to put the db back into multi user mode.
http://blogs.msdn.com/b/dfurman/archive/2012/01/20/getting-out-of-single-user-mode.aspx
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply