Is Database Mirroring Causing The Transaction Log To Fill Up

  • dbgrl93

    Old Hand

    Points: 357

    Comments posted to this topic are about the item Is Database Mirroring Causing The Transaction Log To Fill Up

  • Perry Whittle

    SSC Guru

    Points: 233824

    Very good article, well done.

    Just want to clear up a couple of issues though.

    C.J. Garcia, 2014/11/06


    Database Mirroring is a feature to increase database availability by setting up a principal database and a mirrored database. When the principal database goes down, SQL Server will switch to the mirrored database to minimize down time for critical databases.

    When setting up just a principal and a mirror database in a mirror session, SQL server will not switch the databases during a failure, it requires manual DBA intervention.

    C.J. Garcia, 2014/11/06


    Optionally, there is a third server that acts as a Witness to the communications between the principal and mirror databases when enabling the Automatic Failover option.

    The witness maintains quorum, this doesn't just cover communications between the mirror and principal but also now extends to the witness too. A level of communication has to exist between all 3 or certain actions are taken depending on where the loss of communication lies, Books Online has full details of this.

    C.J. Garcia, 2014/11/06


    The first operating mode is high-performance (asynchronous) mode, where there is just a principal server and a mirror server.

    There's actually nothing stopping you using a witness for an asynch mirror session, although Microsoft don't recommend it at all.

    C.J. Garcia, 2014/11/06


    If for some reason, the link between the two databases is affected, the transaction log will continue to grow on the principal database until it receives acknowledgement from the mirrored database. As a result the transaction log will grow and not re-use space.

    This will still happen in an asynch session where the miorror is unavailable

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • dbgrl93

    Old Hand

    Points: 357

    Thanks for the feedback!!!

Viewing 3 posts - 1 through 3 (of 3 total)

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