Cannot change back to Multi User

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Cannot change back to Multi User

Cannot change back to Multi User

Rate Topic

Display Mode

Topic Options

Author

Message

jillk

Posted Thursday, October 13, 2011 8:13 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, April 18, 2013 8:55 AM
Points: 16, Visits: 133

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

GSquared

Posted Thursday, October 13, 2011 8:27 AM

SSCoach

Group: General Forum Members
Last Login: Today @ 12:57 PM
Points: 15,441, Visits: 9,570

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

MysteryJimbo

Posted Thursday, October 13, 2011 9:08 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 6:28 AM
Points: 1,125, Visits: 14,511

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

jillk

Posted Thursday, October 13, 2011 9:29 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, April 18, 2013 8:55 AM
Points: 16, Visits: 133

Thanks MysteryJimbo! That fixed my problem!!!!!

Post #1189972

Tara-1044200

Posted Tuesday, December 04, 2012 3:26 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:37 PM
Points: 983, Visits: 2,924

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

Terence Keys

Posted Tuesday, December 04, 2012 4:50 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 11:58 PM
Points: 17, Visits: 276

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 »

Permissions