Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cannot change back to Multi User Expand / Collapse
Author
Message
Posted Thursday, October 13, 2011 8:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 19, 2013 1:22 PM
Points: 16, Visits: 138
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!
Post #1189873
Posted Thursday, October 13, 2011 8:27 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #1189894
Posted Thursday, October 13, 2011 9:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:08 AM
Points: 1,350, Visits: 15,200
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
Post #1189939
Posted Thursday, October 13, 2011 9:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 19, 2013 1:22 PM
Points: 16, Visits: 138
Thanks MysteryJimbo! That fixed my problem!!!!!
Post #1189972
Posted Tuesday, December 04, 2012 3:26 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, January 09, 2014 7:44 AM
Points: 988, Visits: 2,945
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?
Post #1392327
Posted Tuesday, December 04, 2012 4:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 10, 2014 2:08 AM
Points: 19, Visits: 330


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

Post #1392375
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse