locked
After Failover Primary went Single user mode RRS feed

  • Question

  • Hi,
    I have 3 database server with mirroring, one is for Primary, 2'nd one for secondary and 3'rd one for Witness. Everything was working fine, but today I faced an issue, there was a failover happened earlier and it switched over to secondary and make that as primary, but the problem is the new primary went to single user mode.I can't bring it in multiuser mode. I ran sp_who2 but no sessions were connected to that database. After restarting the primary it came to multi-user mode.

    My question: Why the database went Single user mode and
    why I can't find any sessions on that database in sp_who2
    How it became multiuser mode after restarting.
    can anyone help me to figure out this? I don't have any logs, after restarting its gone.

    • Edited by Aadhira Thursday, June 23, 2016 4:14 AM
    Thursday, June 23, 2016 4:13 AM

Answers

  • I dont think a database can go into single user mode by itself. Can you open master and run sp_readerrorlog and post its content here.

    Also post output of below queries. Please post it neatly

    select session_id, command,user_id from sys.dm_exec_requests where database_id=db_id(N'db_name')
    go
    select spid,status,login_name,hostname,nt_username from sys.sysprocesses where dbid=db_id(N'db_name')
    Stop using sp_who2 its outdated. Start using DMV's



    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    • Edited by Shanky_621MVP Thursday, June 23, 2016 4:34 AM
    • Marked as answer by Aadhira Thursday, June 23, 2016 6:38 AM
    Thursday, June 23, 2016 4:33 AM
  • Shanky , Thanks for the reply.  This was handling by my onsite team, We already restarted it. So I can't the logs, I'm asking, is anyone know how its possible? 
    At least you can paste output of sp_readerrorlog 1 so that i can see what was in logs before the current restart. Like I said database cannot itself go into single user mode unless some thing forced it into that situation reading logs I might get the idea.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by Aadhira Thursday, June 23, 2016 6:38 AM
    Thursday, June 23, 2016 6:24 AM

All replies

  • I dont think a database can go into single user mode by itself. Can you open master and run sp_readerrorlog and post its content here.

    Also post output of below queries. Please post it neatly

    select session_id, command,user_id from sys.dm_exec_requests where database_id=db_id(N'db_name')
    go
    select spid,status,login_name,hostname,nt_username from sys.sysprocesses where dbid=db_id(N'db_name')
    Stop using sp_who2 its outdated. Start using DMV's



    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    • Edited by Shanky_621MVP Thursday, June 23, 2016 4:34 AM
    • Marked as answer by Aadhira Thursday, June 23, 2016 6:38 AM
    Thursday, June 23, 2016 4:33 AM
  • Shanky , Thanks for the reply.  This was handling by my onsite team, We already restarted it. So I can't the logs, I'm asking, is anyone know how its possible? 
    Thursday, June 23, 2016 5:01 AM
  • Did you check your Windows Event Logs to see if anything happened to the server? Have you looked into cluster.log?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, June 23, 2016 5:21 AM
  • Shanky , Thanks for the reply.  This was handling by my onsite team, We already restarted it. So I can't the logs, I'm asking, is anyone know how its possible? 
    At least you can paste output of sp_readerrorlog 1 so that i can see what was in logs before the current restart. Like I said database cannot itself go into single user mode unless some thing forced it into that situation reading logs I might get the idea.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by Aadhira Thursday, June 23, 2016 6:38 AM
    Thursday, June 23, 2016 6:24 AM
  • Can you post the errorlog ? One reason that can force database in single user is when it is trying to repair something within the database. Although I have never seen this, that is why I asked for logs. What is output of select @@Version. 

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, June 23, 2016 6:46 AM
  • Im sorry shankey, My onsite team didn't allow me to get the logs.

    Thursday, June 23, 2016 7:26 AM