Post reply

Single user mode database

  • July 13, 2009 at 4:31 am

    #208118

    I have taken the one of the databases in "Single User Mode" and now suddently the connection went off.

    Now unable to connect to the db, as the session was closed\blocked.

    Is there is any connect to that and make it multi_user?

    Regards

    Sourav

    Thanks.

  • July 13, 2009 at 4:41 am

    #1023773

    Hi,

    When a database is in Single_user_mode, if there are no connections , you can connect to it.

    The first connection to gain access will be the only conn. that can do operation over database.

  • July 13, 2009 at 4:51 am

    #1023775

    1. Take the DB to single_user mode.

    2. The session expires.

    3. Try to connect again. As it was in Single_user mode you should not connect. (if not wrong).

    4. How to reconnect?

    I think by restarting the server also ill not help here..

    Regards

    Sourav

    Thanks.

  • July 13, 2009 at 6:10 am

    #1023811

    You need to find the application or user that is connecting and stop that application or service. You should be able to connect at that point.

    You could get luck by restarting the SQL Server service and quickly try and connect to the database before the other service grabs the connection.

  • July 13, 2009 at 6:39 am

    #1023828

    yap, that's an easy way.

    I don't know if you can go to the Management/activity monitor and Kill the connection that is connected to that database and then you trie to connect.

  • July 13, 2009 at 12:39 pm

    #1024095

    Thank you Guys!! But I am not confident in either way.

    Let me explain you the scenariooo...

    One of my colleagues faced the problem..

    1. He taken one of the DB into "Single_User" mode.

    2. Somehow the session was expired.

    3. While trying to connect to the db again..he can't as he was connected in "Single_User"..Though he had the admin right,, already it was logged by his credential.

    I never faced such scenario.. I tried to repro the issue doing some manipulation..but can't.

    Any further suggestions..

    Regards

    Sourav

    Thanks.

  • July 13, 2009 at 12:47 pm

    #1024098

    The database is in single_user mode, but that does not prevent you from connecting on another Database (master).

    Connect to the master database, and run sp_who2, check the SPID of the connection that is using the database you want, kill it, and take the control over the single_user database, to change it to multi_user.

    How's that?

    Cheers,

    J-F

  • July 13, 2009 at 2:06 pm

    #1024135

    That is fine..infact as to run sp_who2 we can run that in any DB.

    But unable to repro the first situation that .. when the session expires.

    Anyways,

    Can you suggest one more thing please...?

    sp_who2, sp_locks, are used in SQL 2000. But in 2005 and 2008 we have the DMVS. What are the equivalent DMVs to check for the --

    1. Locking

    2. Blocking.

    3. Deadlocking.

    Regards

    Sourav

    Thanks.

  • July 13, 2009 at 9:56 pm

    #1024229

    Kill the spid that comes in the output of below query:

    select * from sys.sysprocesses

    where dbid=db_id('DatabaseName')

    MJ

Viewing 9 posts - 1 through 8 (of 8 total)

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