Getting SQL Server 2005 Database out of Single-User Mode

  • webrunner

    One Orange Chip

    Points: 29900

    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 says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Mark Harr

    SSCrazy Eights

    Points: 9760

    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

    One Orange Chip

    Points: 29900

    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 says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Robert Davis

    One Orange Chip

    Points: 28027

    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[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Mark Harr

    SSCrazy Eights

    Points: 9760

    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

    One Orange Chip

    Points: 28027

    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[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Mark Harr

    SSCrazy Eights

    Points: 9760

    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

    One Orange Chip

    Points: 28027

    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[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • scooby42

    SSC Enthusiast

    Points: 125

    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

    Valued Member

    Points: 65

    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!!

    🙂

  • scooby42

    SSC Enthusiast

    Points: 125

    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

  • Bob Griffin

    SSCertifiable

    Points: 5863

    I see what you are trying to do with the script....I little more background info would be great though....set the stage...;)

  • Half Bubble

    SSCommitted

    Points: 1948

    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]

  • 2 Tim 3:16

    SSCarpal Tunnel

    Points: 4401

    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

  • Billy Tee

    SSC Rookie

    Points: 38

    Very useful tip. Thanks a bunch.

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply