locked
Database Mirroring RRS feed

  • Question

  • Hi all,

    Principal server log file is increasing very huge, but we tried to shrink log file was not shrinking because principal database is having open transactions and blockings, but log file is increasing huge on principal database. Can any one explain how to resolve this issue?


    Raveendra

    Saturday, May 12, 2012 7:40 AM

Answers

  • Hello,

    Make sure you are taking transaction log backups periodically, this will help to keep the size of the transaction log file size controlled. If you are taking transaction log backups already, you might have to break the mirror, switch the db recovery model to simple and then shrink the log.

    Use below script to check the percentage of the transaction log usage as well as additional information from all your databases

    http://sql-javier-villegas.blogspot.com.ar/2012/03/databases-info.html


    Javier Villegas | @javier_villhttp://sql-javier-villegas.blogspot.com/

    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    • Marked as answer by Maggie Luo Thursday, May 24, 2012 12:18 PM
    Sunday, May 13, 2012 12:39 AM
  • In which mode you have configured database mirroring?

    It seems your transactions are not moved to mirror server hence it's kept in log file. Check whether db mirroring end point is in stopped state, that might give you a clue.

    If this is an urgent issue, the for time being break mirroring and reconfigure it else trouble shoot why the transactions are not moved to mirror server


    Mark as ANSWER if I helped you today :-) www.sql-articles.com

    • Marked as answer by Maggie Luo Thursday, May 24, 2012 12:19 PM
    Thursday, May 17, 2012 12:46 PM

All replies

  • Do you have a transaction log backup in place? Since the database is in Full Recovery Model (as required to configure database mirroring), the only way to re-use the transaction-log space is by initiating the t-log backup. Although it can reuse the space from inactive transaction, for active transaction, the t-log space can grow, but in a controlled way.

    SKG: Please Marked as Answered, if it resolves your issue. (b:http://sudeeptaganguly.wordpress.com )

    Saturday, May 12, 2012 8:22 PM
  • Hello,

    Make sure you are taking transaction log backups periodically, this will help to keep the size of the transaction log file size controlled. If you are taking transaction log backups already, you might have to break the mirror, switch the db recovery model to simple and then shrink the log.

    Use below script to check the percentage of the transaction log usage as well as additional information from all your databases

    http://sql-javier-villegas.blogspot.com.ar/2012/03/databases-info.html


    Javier Villegas | @javier_villhttp://sql-javier-villegas.blogspot.com/

    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    • Marked as answer by Maggie Luo Thursday, May 24, 2012 12:18 PM
    Sunday, May 13, 2012 12:39 AM
  • Javier,

    Is breaking the database mirroring is only way to shrink the log? I think a t-log backup followed by a shrink log should work, if there is no active transactions.


    SKG: Please Marked as Answered, if it resolves your issue. (b:http://sudeeptaganguly.wordpress.com )

    Sunday, May 13, 2012 4:49 AM
  • If the database is configured in full recovery model the shrink operation does not work (the DBCC SHRINKFILE will success but the log file size won't change) This is why I switch to simple. I try to avoid doing this operation by keeping the log file size under control (with transaction log backups every few minutes)

    Javier Villegas | @javier_villhttp://sql-javier-villegas.blogspot.com/

    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Sunday, May 13, 2012 4:45 PM
  • Also I would check to see if there are pending mirroring transactions (large Send Queue) in your log file, this will also prevent the space from being reused.  You can check in Mirroring Monitor or possibly run the following perfmon query

    select * from sys.dm_os_performance_counters
    where object_name = 'SQLServer:Database Mirroring'
    and counter_name = 'Log Send Queue KB'
    



    Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

    Tuesday, May 15, 2012 1:37 PM
  • In which mode you have configured database mirroring?

    It seems your transactions are not moved to mirror server hence it's kept in log file. Check whether db mirroring end point is in stopped state, that might give you a clue.

    If this is an urgent issue, the for time being break mirroring and reconfigure it else trouble shoot why the transactions are not moved to mirror server


    Mark as ANSWER if I helped you today :-) www.sql-articles.com

    • Marked as answer by Maggie Luo Thursday, May 24, 2012 12:19 PM
    Thursday, May 17, 2012 12:46 PM
  • Could you please check SELECT * FROM sys.databases and see what the log_reuse_wait_desc says for that DB? Also please run DBCC OPENTRAN on that DB and send us the result.

    thx

    Monday, May 21, 2012 6:07 AM