none
Truncating or reducing transaction log file size on Primary shipping instance of log shipping db server RRS feed

  • Question

  • Setup: I have a primary server (Server A) doing 15min tran log backups to a network share where Server B and Server C check for and restore the contents of every 15 mins as well.

    - Also nightly at 8pm I run a full DATABASE backup to a network share

    Basically  it's the default config and setup when going through the GUI to setup log shipping.

     

    My issue is that the Transaction log life (.ldf) is continually growing for reach database, and is on track to overtake the disk at some point down the road.

     

    I know this question has been asked many times I'm sure, but i need to know how to reguarly reduce the size of the .ldf file.  I thought log shipping would have done this, but apparently not.

    From what I can gather, running a full backup of the database LOG will free the space up...but with a tran log shipping environment...would i also need to keep that .bak file as well?  Or should I be looking at using the truncate command? (from what i read this will break my log shipping which is no good)

     

    Thoughts? anyone have any helpful tips?  I appreciate any tips or suggestions!

    Friday, October 21, 2011 6:23 PM

Answers

  • I am going to assume that it's SQL 2005 or above.
    If you are taking transaction log backups regularly then it would mark the VLF as inactive so that it can be resued, provided there is nothing else which need to be done with that VLF.
    You can query sys.databases and look for value in last_reuse_wait_desc column to find out why it not reusing it.

    Full and log backup can go in parallel in SQL 2005 onwards. No harm caused to log shipping. As Anup mentioned, never use truncate_only while taking the log backup because it would break the log-shipping and you need to reconfigure it. Here is another KB

    The transaction log may grow without a log backup for a database using the FULL recovery model
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    • Proposed as answer by Warwick Rudd Saturday, October 22, 2011 11:57 AM
    • Marked as answer by Stephanie Lv Saturday, October 29, 2011 5:47 AM
    Saturday, October 22, 2011 8:03 AM
    Moderator

All replies

  • Answers to your comments - 

    'From what I can gather, running a full backup of the database LOG will free the space up'   - A full backup doesnt clear tran_log.Tran log is cleared only if there is a tran log backup or a checkpoint is applied.

    Or should I be looking at using the truncate command?' - Dont ever use truncate command for gaining space.

    Now recommendations  - 

    When you do a Tran_Log backup it will truncate the log and will gain space.The actual file size,ie size of ldf file depends on your environment.If you have heavy transactions,then the file size will grow if you have set autogrowth.

    You will need to understand why tran log file size is increasing over the period of time.Are there any long running transactions etc.

    Few reads are - 

    http://support.microsoft.com/kb/317375

    http://sqlskills.com/BLOGS/PAUL/post/Importance-of-proper-transaction-log-size-management.aspx


    Your logshipping environment looks pretty good.You are doing the right method of backing it up and moving to a share and another servers copying and doing the restore.This is a perfect way how log shipping works.

     

    Thank you,



    Anup | Forum Support| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer.
    Friday, October 21, 2011 7:01 PM
  • I think the tran log .ldf file is increasing in size just due to time...full .bak backups of the tran log are not preformed at all currently (as i thought i was accomplishing this by doing .trn log file shipping).

     

    So if i schedule up a full .bak backup of the .ldf transaction log...

    1. Do i need to keep this file, as I'm also doing full database backups + transaction log shipping to two remote servers?  I can't see why i would need it especially considering the .trn files used for log shipping are kept around for 2 days also.

    2. Will running the full backup .bak of the .ldf transaction log file cause any sequencing errors with the log shipping?  I've got mixed results in my searches.

     

    Thanks!!

    Friday, October 21, 2011 7:37 PM
  • I am going to assume that it's SQL 2005 or above.
    If you are taking transaction log backups regularly then it would mark the VLF as inactive so that it can be resued, provided there is nothing else which need to be done with that VLF.
    You can query sys.databases and look for value in last_reuse_wait_desc column to find out why it not reusing it.

    Full and log backup can go in parallel in SQL 2005 onwards. No harm caused to log shipping. As Anup mentioned, never use truncate_only while taking the log backup because it would break the log-shipping and you need to reconfigure it. Here is another KB

    The transaction log may grow without a log backup for a database using the FULL recovery model
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    • Proposed as answer by Warwick Rudd Saturday, October 22, 2011 11:57 AM
    • Marked as answer by Stephanie Lv Saturday, October 29, 2011 5:47 AM
    Saturday, October 22, 2011 8:03 AM
    Moderator