locked
No primary database after failover RRS feed

  • Question

  • I am working on an issue with a mirrored database setup where it appears after an automatic fail over of the system neither copy of the database was acting as the primary. I went through the logs and found when the fail over happened and it does show that one of the databases should be in primary status but I was unable to perform any queries against that database and the application that was using the database was not functioning. When I would try to query the database I would get an exception stating that the database could not be opened because it was acting as a mirror database (Error: 954). I forced a fail over from the listed primary to the listed mirror and everything started working again. 

    Has anyone experienced an issue like this before? What might be the root cause and what can be done to prevent this from happening in the future?
    Tuesday, November 22, 2016 3:48 PM

All replies

  • Do you have a witness server, right? 

    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

    Wednesday, November 23, 2016 7:38 AM
  • Hi Greg,

    According to your description, this issue can be cause by that the automatic failover did not been operated successfully. Could you please share the error message on witness, principal, mirror server to us for further analysis? We can find it from C:\Program Files\Microsoft SQL Server\MSSQL**.**\MSSQL\Log.

    Based on the current information, we can infer the following scenarios:

    1. The principal server loses the connection to Witness, when the principal server fails, the witness server did not switch the role of mirror and principal, then the client still connects to the original server.

    2. This scenario did not meet the following requirements of automatic failover:

    a. The database mirroring session must be running in high-safety mode and must possess a witness.
    b. The mirror database must already be synchronized. This guarantees that all of the log sent to the mirror server has been written to disk.
    c. The principal server has lost communication with the rest of the database mirroring configuration, while the mirror and witness retain quorum.
    d. The mirror server has detected the loss of the principal server.

    For more information, please refer to this article.

    Best Regards,
    Teige

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 23, 2016 8:39 AM