Database stuck in single user mode

  • Hi,

    Suppose you had a database stuck in single user mode that is in a busy OLTP environment.

    Given that:

    1. The connection that originally put the database into single user mode is gone.

    2. The DAC has not been set up on that instance.

    3. It is almost guaranteed that if you kill the spid holding the only connection to the database, another connection will grab it in milliseconds.

    How would you get the db back into multi-user mode?

    Thanks,

    Andy

  • You shouldn't need to be in the database to change to multiple users. Connect to the master database as a system administrator and then alter the properties of the database.

  • But another SPID already has the only available connection to that database. Running the alter database command from master even logged in as a sysadmin would not work.

    Msg 5064, Level 16, State 1, Line 1

    Changes to the state or options of database 'test' 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.

  • LOL, that kind of defeats the purpose of connecting to master.

    I think the only thing you can likely do here, is connect as admin, then pause the server. That prevents new connections. Once it's paused, kill the connection in that database, and you ought to be able to get int. You should be able to then restart the server.

  • Something like this is likely to work

    use master

    kill 75 -- the connection to the database in single user mode

    use [MyDatabase]

    alter database [MyDatabase] set multi_user with rollback immediate

    If that doesn't work, try this:

    use master

    kill 75 -- the connection to the database in single user mode

    alter database [MyDatabase] set offline with rollback immediate

    alter database [MyDatabase] set online, multi_user with rollback immediate

    Another thing you could do is disable the logins that have access to the database, kill the active database connection, and then alter the database.

  • Hi, I'm having this exact problem. How do you identify the connection that is consuming the single-user connection? Is it 75 by default?

    Thanks

  • Query sysprocesses and see what connection is connected to that database.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • sysprocesses is a server-level view, it's not database-specific. It returns all of the sessions connected to the server and, as one of the columns, has the id of the database that the session is currently connected to.

    select * from master.sys.sysprocesses

    where spid>50 -- don't want system sessions

    and dbid = DB_ID(<name of database in question>)

    I'm using sysprocesses (a compatibility view) because I don't know how to get the DBID of a sleeping connection via the 2005 DMVs

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks for the quick reply. But the problem still is I cannot login to the SQL db at all because it's in Single-user mode, so how can I execute a SQL select statement if I cannot login???

    If I could execute a select statement to look at the sysprocesses I would be able to execute the Alter database command to set it to Multi-User mode.

    It's driving me crazy!

    Jimmie

  • jimmie.roberts (6/25/2009)


    Thanks for the quick reply. But the problem still is I cannot login to the SQL db at all because it's in Single-user mode, so how can I execute a SQL select statement if I cannot login???

    You don't need to connect to that database. Run it from any database on that instance. Run it from master. sysprocesses is a server-wide view, you'll get exactly the same results from it no matter what database you run it from.

    If I could execute a select statement to look at the sysprocesses I would be able to execute the Alter database command to set it to Multi-User mode.

    No. If that was the case I wouldn't be telling you to query sysprocesses. All you need to query sysprocesses is a connection to a database, any database. Master's fine. So all you need to be able to do is log into the instance.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I use SQL Studio and then Object Explorer to navigate thru SQL. Right after I excuted this command:

    ALTER DATABASE TEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    I could see in Object Explorer that the TEMP DB was in Single-User mode. But, when I right clicked the db to perform a task, (a "force" restore), I got an error saying that I couldn't access the TEMP DB. I closed SQL Studio and then restarted it and now when it tries to connect to the Instance, I get an error that says login failed for user 'sqladmin' Cannot open default database. I am configured to connect via Windows Authentication but my username is greyed-out (I cannot modify it) and the password field is blank. I try connecting via SQL authentication and that fails also.

    That ALTER DATABASE command really messed me up.

    Do you know how I can modify who I am logging in as?

    Thanks,

    Jimmie

  • In the management studio login dialog, click on Options (bottom right). That will take you to another dialog where you can change the default database. Pick master.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thank you! That was it. Duh.

    Now, I can go into the properties of my DBs and then I click on Options and I can set the Restrict Access setting to Multi-User!

    That's it! Thank you again.

    Jimmie

  • You can identity it by running the sysprocesses query.

    When you run the query you will receive the same message saying "database is in single_user mode".

    If you check the resultset you will get some spid see the last spid add one in that you will get the spid accessing the single_user database.

    Kill the spid

    I hope this works.

    Abhijit - http://abhijitmore.wordpress.com

  • you can execute sp_who2 to see the processes

    --Divya

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

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