SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Getting SQL Server 2005 Database out of Single-User Mode


Getting SQL Server 2005 Database out of Single-User Mode

Author
Message
webrunner
webrunner
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7537 Visits: 3997
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!

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"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
Mark Harr
Mark Harr
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2600 Visits: 191
  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
webrunner
webrunner
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7537 Visits: 3997
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.

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"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
Robert Davis
Robert Davis
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6228 Visits: 1632

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 MVP
Database Engineer at BlueMountain Capital Management
Mark Harr
Mark Harr
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2600 Visits: 191

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
Robert Davis
Robert Davis
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6228 Visits: 1632
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 MVP
Database Engineer at BlueMountain Capital Management
Mark Harr
Mark Harr
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2600 Visits: 191

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
Robert Davis
Robert Davis
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6228 Visits: 1632
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 MVP
Database Engineer at BlueMountain Capital Management
scooby42
scooby42
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 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.
fearlessfish
fearlessfish
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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!!

Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search