December 21, 2006 at 7:26 am
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!
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
December 21, 2006 at 7:48 am
Hope this helps
Mark
December 21, 2006 at 7:56 am
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.
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
December 22, 2006 at 12:22 pm
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
December 22, 2006 at 3:30 pm
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
December 22, 2006 at 3:35 pm
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.
December 22, 2006 at 3:57 pm
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
December 22, 2006 at 4:02 pm
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.
November 19, 2007 at 10:12 am
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.
January 30, 2008 at 10:19 pm
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!!
🙂
February 1, 2008 at 11:15 am
I enhanced a script to tell you who's in a database you are trying to use. It also includes fixing orphaned SQL logins that my need to be sync'd with logins for the instance you are restoring to.
USE Master
DECLARE @dbid INT
SELECT @dbid = dbid FROM sys.sysdatabases WHERE name = ' '
IF EXISTS (SELECT spid FROM sys.sysprocesses WHERE dbid = @dbid)
BEGIN
RAISERROR ('The restore cannot be done', 16, 1)
SELECT 'These processes are blocking the restore from occurring' AS Note, spid, last_batch, status, hostname, loginame FROM sys.sysprocesses WHERE dbid = @dbid
END
ELSE
BEGIN
PRINT 'The database is in not in use by any processes. Beginning restore'
-- The restore location may be different depending on your SQL Server configuration
RESTORE DATABASE _Log.ldf', NOUNLOAD, REPLACE, STATS = 10
PRINT 'Fixing the SQL login'
USE
EXEC sp_change_users_login @action = 'auto_fix', @UserNamePattern = ' '
PRINT 'Reporting on logins that still need to be fixed, but may not apply
EXEC sp_change_users_login @action = 'report'
END
February 1, 2008 at 11:20 am
I see what you are trying to do with the script....I little more background info would be great though....set the stage...;)
June 30, 2009 at 8:40 am
Just rescued me! I had one stuck in single user mode. Don't know how it got that way, but now it's fixed.
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
March 31, 2010 at 10:17 am
This thread got me thinking...why couldn't you use the sp_msforeachdb procedure to set all user DB's in single_user??
So, I tried it;
exec sp_msforeachdb 'alter database ? set single_user with rollback immediate'
it gave me an informational error on the master and tempdb, but it did indeed set all user DB's to single_user. (and model, msdb)
If I use a qualifier like this;
exec sp_msforeachdb 'if ''?'' not in(''master'',''model'',''msdb'',''tempdb'')
alter database ? set single_user with rollback immediate'
It will not set model and msdb in single_user.
so far, so good.
The problem occurs when I try to set them all back to multi_user.
nothing seems to work using the sp_msforeachdb method.
I have to set them all back one at a time. Arghh!
It appears the sp cannot access the db because it is in single_user.
anyone else try this method?
Tim White
October 1, 2010 at 8:38 am
Very useful tip. Thanks a bunch.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply