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 12»»

Getting SQL Server 2005 Database out of Single-User Mode Expand / Collapse
Author
Message
Posted Thursday, December 21, 2006 7:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:08 AM
Points: 2,330, Visits: 2,664
Hello,

I put a SQL Server 2005 database into single-user mode using Management Studio in an attempt to force a restore over the existing database.

But now I can't access the database. I get these errors:

When first clicking the database in the tree:
"The database ___ is not accessible (Object Explorer)"

Then if I try to right-click on the database and view Properties, I get a longer message ending with:
"Database '__' is already open and can only have one user at a time. (Microsoft SQL Server, Error: 924)."

But I was the user who put the database into single-user mode, and that's the same user that comes up in the '1 Active Connection' box when I go to Detach the database (not to really detach but to see what the connection is). So I don't know why it won't let me perform any actions on the database.

Finally, I tried:
ALTER DATABASE ___
SET MULTI_USER

But I get the error:
"Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database '___' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed."

Does anyone know how to revert a database to Multi-User mode in SQL Server 2005? Also, this DB is a back end to a .NET application -- is that application the user that is using up the single-user connection? If so, how can I stop that connection or otherwise set the database back to Multi-User mode?

Thanks in advance for any help!


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #332162
Posted Thursday, December 21, 2006 7:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 12:21 PM
Points: 1,327, Visits: 155
  1. Use SP_Who to find the session ID of the active connection to the database
  2. Use KILL <session id> to terminate the connection.
  3. Then do the Alter Database command you tried.

Hope this helps





Mark
Post #332171
Posted Thursday, December 21, 2006 7:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:08 AM
Points: 2,330, Visits: 2,664
Thank you SO MUCH! That worked perfectly.

I had tried to kill the process using the Management Studio interface without success. But for some reason issuing the command in the query window works.

Thanks again for your help.


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #332177
Posted Friday, December 22, 2006 12:22 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:16 AM
Points: 1,612, Visits: 1,537

This works a lot smoother if you do it all in one fell swoop. Like so:

Use MyDatabase

Alter Database MyDatabase Set Singel_User With Rollback Immediate

Restore Database MyDatabase ......... blah blah blah

Alter Database MyDatabase Set Multi_User





My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #332607
Posted Friday, December 22, 2006 3:30 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 12:21 PM
Points: 1,327, Visits: 155

Yes, that will usually work better.  But you still have to be prepare to lookup and kill a connection that might have snuck in. 

This was the problem with SQL Server 2000 maintenance plans, where some would unknowningly check the box for "automatically fix minor errors" under the "Check database for errors" options.  For SQL server to fix errors, it had to put the database in single user mode.  If the database was busy at the time, sometimes a user would be able to get in between the Alter database and DBCC CheckDB commands and grab single-user control, killing the job, and blocking out all other users.  As a consultant, I have been called to several sites that have had this problem.  Then they feel plenty bad about paying me $160/hour w/4hr minimum for me to come in and uncheck a check box.





Mark
Post #332655
Posted Friday, December 22, 2006 3:35 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:16 AM
Points: 1,612, Visits: 1,537
That's why I said do it all in one fell swoop. I'm using the database, so if anyone else tries to become a user, they are blocked.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #332658
Posted Friday, December 22, 2006 3:57 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 12:21 PM
Points: 1,327, Visits: 155

Yes, and I said it will usually work.

But not always. You do not have any guarantee that in between the execution of the Alter database statement (putting it in single use) and the next statement, that another person or process can grab the only process.  Set Single User does not say "reserve the database for me".  It only says "only one connection open at a time."  But that one connection can be anyone.





Mark
Post #332661
Posted Friday, December 22, 2006 4:02 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:16 AM
Points: 1,612, Visits: 1,537
I run the whole thing as a single transaction, and my connection isn't released until it completes. It has never failed for me when running it as a single transaction.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #332662
Posted Monday, November 19, 2007 10:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 13, 2010 3:33 PM
Points: 7, Visits: 126
I am having a similar problem where someone managed to put a databse into single user mode. Running sp_who and sp_who2 tell me the database is already open and can only have one user at a time. Trying to go into Activity Monitor gives me the same problem.

RESOLVED : I had to use Dedicated Admin Connection to take the database back to multi user mode.
Post #423732
Posted Wednesday, January 30, 2008 10:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 5, 2008 10:14 AM
Points: 1, Visits: 3
THANK YOU! THANK YOU!!

That worked for me too! When it's after midnight you're always extra excited to find good info like this!!

:)
Post #449800
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse