locked
Principle log file is Growing on Mirroring RRS feed

  • Question

  • Hi all,

    I have 2 years of experience on Sql server, now a days we have to handle L2 support.

    I have faced one issue on last night, Principle log file is growing continuously how can i handle this type of situation and why the log file is growing? Please give me reply soon..we have to handle this issue today.

    Tuesday, February 7, 2012 8:04 AM

Answers

  • Maybe it would be a good start to find out why the logfile is growing and what is preventing the reuse... There are multiple reasons for this:

    1) Open transactions

    2) Mirroring out of sync

    3) Replication

    4) Missing log backups

    The easiest way to find that out is to do a SELECT log_reuse_wait_desc from sys.databases WHERE name=<YourDBName>

    • Marked as answer by amber zhang Wednesday, February 15, 2012 8:04 AM
    Friday, February 10, 2012 9:01 AM
  • Hi Ravi Chunduri,

    Regarding to your description, as Uri mentioned you can try to use TSQL BACKUP LOG. But the log backup that truncating the log is not physically shrinking the log. It's good to understand the Truncating the Transaction Log.

    Another thing is that regularly performing physical shrinks of your logs is not necessarily a good idea. You should manage the log size with your transaction log backups. You don't want the logs continually growing; shrinking. You want the logs to be at the appropriate size need for the database activities and managed with log backups. Please refer to the article about shrinking files.


    Regards, Amber zhang

    • Marked as answer by amber zhang Wednesday, February 15, 2012 8:04 AM
    Wednesday, February 8, 2012 7:55 AM

All replies

  • One reason probably due to rebuild indexes

    BACKUP LOG TO DISK = 'Nul', then run a DBCC SHRINKFILE command. 


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

    Tuesday, February 7, 2012 8:14 AM
  • Scheduled log backups that is the way to mark unused free space to re-use so your log file does not grow up too much

    http://uk.linkedin.com/in/ramjaddu

    Tuesday, February 7, 2012 11:35 AM
  • If you want to shkink the transaction log now, you have to break the mirror, switch the database recovery model to simple, shrink the log file, switch back the recovery model to full , take a full backup+transaction log backup and reestablish the mirror.

    Then yoo have to monitor the transaction log file usage and size. As mentioned in a previous reply you must schedule to take transaction log backups frecuently in order to control the size

    Regards


    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

    Tuesday, February 7, 2012 11:54 AM
  • Hi Ravi Chunduri,

    Regarding to your description, as Uri mentioned you can try to use TSQL BACKUP LOG. But the log backup that truncating the log is not physically shrinking the log. It's good to understand the Truncating the Transaction Log.

    Another thing is that regularly performing physical shrinks of your logs is not necessarily a good idea. You should manage the log size with your transaction log backups. You don't want the logs continually growing; shrinking. You want the logs to be at the appropriate size need for the database activities and managed with log backups. Please refer to the article about shrinking files.


    Regards, Amber zhang

    • Marked as answer by amber zhang Wednesday, February 15, 2012 8:04 AM
    Wednesday, February 8, 2012 7:55 AM
  • Javier

    Why to break mirroring, backup log file and then DBCC SHRINKFILE...?


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

    Thursday, February 9, 2012 5:57 AM
  • Maybe it would be a good start to find out why the logfile is growing and what is preventing the reuse... There are multiple reasons for this:

    1) Open transactions

    2) Mirroring out of sync

    3) Replication

    4) Missing log backups

    The easiest way to find that out is to do a SELECT log_reuse_wait_desc from sys.databases WHERE name=<YourDBName>

    • Marked as answer by amber zhang Wednesday, February 15, 2012 8:04 AM
    Friday, February 10, 2012 9:01 AM
  • Hi Uri Dim ant,

    Thanks for you help. Can we take the transaction log backup on principle server? I guess, if we take the transaction log backup may be it is impact to mirror server. Can you give me brief explanation?


    Raveendra

    Wednesday, March 21, 2012 11:33 AM
  • Hello Raveendra,

    As you might already aware of that, Database Mirroring requires the database to be in Full Recovery Model. When a database is in Full recovery model, all the transactions are logged in the T-Log file, which will in result grow the t-log file. When you initiate a Transaction log backup, the inactive portion of transaction log file will be cleared and can be reused for future transactions. Thus, you can control the t-log file growth; however, it can grow because of a long running transaction.

    As Uri said earlier, you should take transaction log backup, and make sure you take it in frequent interval. To answer your question, you can take the transaction log backup only on the Principal server, if you are using SQL Server 2005/2008/2008 R2.

    Hope, this may answer your query :)


    SKG: Please Marked as Answered, if it resolves your issue.

    Wednesday, March 28, 2012 9:14 AM
  • Thanks Ganguly for your necessary information.

    Can we truncate the transaction log backup of principal server? if we truncate, what happen exactly on the background and is it impact to mirror server?


    Raveendra

    Saturday, March 31, 2012 3:35 PM
  • Hello Raveendra,

    Backup Log with Tuuncate only will not work as the command is no more supported on SQL Server 2008 onwards. Is there any specific reason you want to truncate the log? As I said earlier, frequent log backup will clean up the inactive portion of the log, so your log file will not grow.


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

    Sunday, April 1, 2012 8:54 AM
  • Thanks Ganguly,

    I have one doubt, in case if we truncate the transaction log file of principle server on 2005 mirroring, what happens exactly background and is it impact to mirror server?


    Raveendra

    Monday, April 2, 2012 5:35 AM
  • Raveendra,

    Why you want to truncate the log file? Every transaction on the Principal database will commit first on the Mirror database (unless you are running Mirrroring in Asynchronous mode). Unless the transaction is committed on the Mirror database, it will not be committted on the Principal Database. Truncating the transaction log file is not the solution, make sure you run t-log backup more frequently.

    Did you try to execute the command suggested by PrinceLucifer, in the earlier reply? What is the output of the query?


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

    Monday, April 2, 2012 7:36 AM