Post reply

Database stuck in single user mode

  • December 22, 2009 at 1:57 pm

    #1095680

    You can get the SPID from activity monitor too.

  • December 23, 2009 at 2:04 am

    #1095828

    I was also facing the same issues even I tried all the solutions mentioned here & some other solution except stopping or pausing the server.

    I used another trick for it.

    1. Query the sysprocessess

    2. You will receive the same message stating "Changes to the state or options of database 'test' cannot be made at this time. bla..bla..bla"

    3. Check the resultset, get the highest spid from the result. add "1" value to it. say if you got spid as 75 you have to kill the spid 76.

    4. Your whole & sole connection to database is killed now.

    5. use "Alter database [MyDatabase] set multi_user with rollback immediate"

    Abhijit - http://abhijitmore.wordpress.com

  • July 12, 2011 at 2:07 pm

    #1351870

    Excellent, worked like a charm. Thanks.

    - Subah Ramakrishnan

  • May 6, 2013 at 10:04 am

    #1612677

    Hi,

    use the same session for which you have used to make the database single_user. By default that session will have the access. run the

    Alter database db_name with multi_user command in same window. Yuo will get the access and database will be in multi user mode.

    Thanks

    Amol Solunkhe

  • May 6, 2013 at 10:19 am

    #1612686

    note you replied to a thread more than two years old.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 30, 2018 at 5:42 am

    #1999437

    j.a.c - Tuesday, June 23, 2009 5:19 PM

    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

    Had same issue today, here's what I did to solve it:-
    1. select * from sys.dm_tran_locks where resource_database_id=db_id('databasename')
    go
    2.kill  <request_session_id>
    3.alter databse <databasename> set multi_user with no_wait

    go

    Execute commands 2 and 3 at the same time. There won't be enough time for the creation of another connection to the database and you should now get
    Commands completed successfully.

    Hope this helps.

Viewing 6 posts - 16 through 20 (of 20 total)

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