Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How to get database out of single user

Отвечено How to get database out of single user

  • Tuesday, May 01, 2012 4:19 PM
     
     
    Sign In to Vote
    0

    Hi

    SQL Server 2005. Had changed database to single user/read only option. Now I cant change database back to multi user and all commands are getting stuck.

    Msg 924, Level 14, State 1, Line 1
    Database 'db1' is already open and can only have one user at a time.

    I have tried all below and failed to get around this issue

    sp_dboption db1, 'single', false

    alter database db1 multi_user with rollback immediate

    I cannot select the spid with as I get Msg 924 with below commands which all get stuck.

    select spid from sysprocesses where dbid = (id of database)

    sp_who does not show

    What can be done to get out of this as this is a production server and difficult to restart midweek.

     

All Replies

  • Tuesday, May 01, 2012 4:30 PM
     
     
    Sign In to Vote
    0

    I'd close down your connection, re-open it, run sp_who against the server hosting the database, then execute 'KILL <spid>' when you find the connection that's got the db open.  Once killed, you can run yuor alter database db1 mult_user statement.

    Let us know how you get on...


    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

     
  • Tuesday, May 01, 2012 4:34 PM
     
     
    Sign In to Vote
    0

    Andrew

    Its not my connection which is connected to the database. The issue is cannot find the spid which is connected to kill it as all selects from system tables generate the Msg 924 error

     
  • Tuesday, May 01, 2012 4:40 PM
     
     
    Sign In to Vote
    0
    you're saying even sp_who generates a 924 error?  or just that it doesn't show the spid connected to the database?


    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

     
  • Tuesday, May 01, 2012 4:48 PM
     
     Answered
    Sign In to Vote
    1

    Or use DAC (Dedicated Admin Connection). Make sure you have enabled it first

    In SSMS type in admin:<instanceName> for Server Name

    after connecting to master ALTER DATABASE <DBName> SET MULTI_USER

    • Proposed As Answer by jgardner04 Tuesday, May 01, 2012 6:26 PM
    • Marked As Answer by Peja TaoModerator Tuesday, May 08, 2012 1:41 AM
    •  
     
  • Tuesday, May 01, 2012 7:27 PM
     
     Answered
    Sign In to Vote
    0

    I had a similar problem when the Query Tab for the single user got closed accidently.

    1. I logged in through DAC. I tried killing a SPID (sp_who2) that I thought had the lock but could not get access to the database and I could not bring the database to muti-user mode.

    2. I logged on as sa, but I could not bring the database to muti-user mode.

    3. I tried re-starting the SQL Server instance (Test Server), the instance would not stop.

    4. I had to reboot the Database Server (Test Server). The database recovered on it's own.

    I was then able to bring the database to single user mode, ran check db and then brought the database back to multi-user mode.

    -Jeelani


     
  • Tuesday, May 01, 2012 9:19 PM
     
     
    Sign In to Vote
    0

    Thansk for the replies. sp_who/2 generates results but does not show the spid which is connected to the database and teminates with the Msg 924.

    I will try the DAC method. Thanks