locked
Behavior of Database mirroring RRS feed

  • Question

  • Hello,

    I would like to know the behavior of database mirroring : 'when database mirroring is configured in high safety mode, when a transaction happened and principal server didn't receive acknowledgement from mirror instance. What will be the behavior of the principal instance ? it waits forever for the acknowledgement or failover to mirror or disconnects/suspends the mirror ? "

    We had a weird situation in our critical production SQL instance. Database is configured with mirroring, on one fine day application or me or anyone is unable to connect to the instance. It was frozen, then i had to restart the service and it failed over to mirror instance and again i failed back the instance to primary, then it was fine.

    Can anyone respond to my first question ? I will give more details to the issue we got here.

    Thanks in advance!!!!


    • Moved by Olaf HelperMVP Friday, January 23, 2015 8:25 AM Moved from "Database Engine" to a more related forum
    Thursday, January 22, 2015 5:11 PM

Answers

All replies

  • Hello,


    If the communication between the principal and the mirror gets broken, or the principal does not get response from the mirror, or the principal cannot send log records as fast as they are created then the Send Queue (part of the transaction log on the principal) will start to grow.

    Hope this helps.



    Regards,



    Alberto Morillo
    SQLCoffee.com

    Thursday, January 22, 2015 6:14 PM
  • Hello,


    If the communication between the principal and the mirror gets broken, or the principal does not get response from the mirror, or the principal cannot send log records as fast as they are created then the Send Queue (part of the transaction log on the principal) will start to grow.

    Hope this helps.



    Regards,



    Alberto Morillo
    SQLCoffee.com

    Thanks Albert.

    Assume, if the mirror instance is slow so the principal waits forever for the acknowledgement from mirror ?

    Thursday, January 22, 2015 6:47 PM
  •  it waits forever for the acknowledgement or failover to mirror or disconnects/suspends the mirror ? "

    It would wait to receive acknowledgement from Mirror and if this takes time and heavy transaction is going on transaction log on principal will increase.

    regarding freezing of your database you have to tell us more. Look at errorlog there must be some information


    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 Article

    MVP

    Thursday, January 22, 2015 6:50 PM
  • Hello,


    The same should happen, the Send Queue should grow, thus the Transaction Log on the principal should rapidly start to grow, unless the principal can start to send log records as fast as they are being created. Based on the experience I have with a customer in New York that had mirroring set between the company headquarters and a DR site, and was experimenting communication issues between both locations, if the communication issue is not solved you can end with some disk drives full, with no free space available, where the transaction log of the principal is located.

    Hope this helps.



    Regards,



    Alberto Morillo
    SQLCoffee.com

    Thursday, January 22, 2015 6:59 PM
  •  it waits forever for the acknowledgement or failover to mirror or disconnects/suspends the mirror ? "

    It would wait to receive acknowledgement from Mirror and if this takes time and heavy transaction is going on transaction log on principal will increase.

    regarding freezing of your database you have to tell us more. Look at errorlog there must be some information


    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 Article

    MVP

    Shanky, i'm aware of the send queue growth in principal database. so log will grow forever or any mirror disconnection ?
    Thursday, January 22, 2015 7:03 PM
  • Shanky, i'm aware of the send queue growth in principal database. so log will grow forever or any mirror disconnection ?

    The log would grow by default SQL Server would never initiate a disconnect of connection. Yes network itself can flip due to network problem.

    Please read Table 9 in below document. It would list out various process that goes in Mirroring in full safety

    https://technet.microsoft.com/en-gb/library/cc917680.aspx


    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 Article

    MVP

    Thursday, January 22, 2015 7:31 PM