none
T-log Backup not reducing size of LDF file RRS feed

  • Question

  • Hello Experts,

    I have already scheduled T-log backup on primary server of an availability group, but still LDF file has grown till disk gets full.

    after that manually taken log backup on secondary and primary both. but again LDF did not reduce and manually shrinked it by removing DB from AG and putting it in simple recovery, kindly suggest how backup maintenance plan should be scheduled so that such issues and manual intervention can be avoided.

    thanks in advance


    SQL Server DBA

    Monday, February 5, 2018 7:58 AM

All replies

  • Hello,

    Log backup Releases only VLF = virtual log files. SQL Server never shrink files on it's own, because it's an expensive Operation.

    Run this query to see why log file space isn't reused:

    select name, log_reuse_wait_desc
    from sys.databases

    How often do you run log backups? On high workload you decrease the times between log backups.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Hannah Yu Monday, February 12, 2018 10:05 AM
    Monday, February 5, 2018 8:12 AM
  • http://blogs.technet.com/technet_blog_images/b/sql_server_sizing_ha_and_performance_hints/archive/2012/12/17/sql-2012-availability-group-backup-transaction-log-on-secondary-shrink-transaction-log-fails.aspx

    And you need to reduce the numbers of VLF on your log file.


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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Hannah Yu Monday, February 12, 2018 10:06 AM
    Monday, February 5, 2018 8:25 AM
  • Hello Experts,

    I have already scheduled T-log backup on primary server of an availability group, but still LDF file has grown till disk gets full.

    What is frequency of T Log Backup ?, may be you need to make it more frequent, may be the transactions produces are lot and the bandwidth is small so getting them replicated and finally getting confirmation that they have been hardened on secondary replica is taking time and thus increasing commit time. Also do check filegrowth of log files they should not be in percentage.

    after that manually taken log backup on secondary and primary both. but again LDF did not reduce and manually shrinked it by removing DB from AG and putting it in simple recovery, kindly suggest how backup maintenance plan should be scheduled so that such issues and manual intervention can be avoided.

    A simple answer cannot be provided for it because AG is configured here it does take more log space than normal standalone databases. What is size of log file ?

    Read This Blog it would help you in understanding. Specially the section "Are we piling up log content on primary replica"


    Cheers,

    Shashank

    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 Articles

    MVP

    • Proposed as answer by Hannah Yu Monday, February 12, 2018 10:06 AM
    Monday, February 5, 2018 9:11 AM
    Moderator
  • Hi Zeal DBA,

    As shanky_621 has suggested there are multiple factors which can affect log file size.

    If your daily operations happening on databases are huge and occupying max(current space in your situation) space then it is useless to shrink the file as on next day while same operation is going on the log file will occupy the max space.

    In AWAG if there is network slowness between node1 to node2 then current log on node1 will keep on increasing unless it is committed on node2.

    If you can estimate the log max file increase on a day then keep the auto growth in terms of GB and not in percentage. This will reduce VLF count.

    Regards,

    AKash Pawar

    • Proposed as answer by Hannah Yu Monday, February 12, 2018 10:06 AM
    Tuesday, February 6, 2018 11:19 AM