none
Shrinking Log Files - SQL Server 2012 Always On Availability Group RRS feed

  • Question

  • Dears,

    I do have 2 SQL Servers (2012) on a Always On Availability Group.

    How can i make a maintenance plan to shrink the Log files?

    Should i need to make a maintenance plan to take the DB backup first and then to shrinking?

    Please let me know the best way to automate the log file shrinking.

    thanks in advance,

    Ammar

    Sunday, February 10, 2019 6:14 AM

All replies

  • https://dba.stackexchange.com/questions/73850/shrink-transaction-log-while-using-alwayson-availability-group

    /*

    In AGs writes can only occur on the primary. Shrink operations are writes. Therefore you must do the shrink on the primary. Note that the shrink may not shrink as much as you expect, your test on the restored DB had probably leveraged simple recovery model. Read How to shrink the SQL Server logfor more info.

    Do not shrink to 160MB. Determine why did the log grow to 121Gb so it does not repeat (you have a suspicion, would be nice to confirm if possible). Size the log to a size appropriate for your operational needs. Log growth is a serious problem, it cannot use instant file initialization and all your database activity will freeze while the log grows and is being 0-initialized. Users and apps hate it when it occurs. If you understand the impact and your users are OK, you can shrink once to a small amount (160MB is probably too small though) and let it grow until it stabilizes.

    */

    Also good reading

    http://www.thecloudtechnologist.com/shrinking-sql-log-files-in-an-availability-group-cluster-or-database-mirror/

    >>>>Please let me know the best way to automate the log file shrinking.

    Create a job on the Primary, and secondary (but check if the instance is currently primary  then only  run the job) 


    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

    Sunday, February 10, 2019 6:37 AM

  • How can i make a maintenance plan to shrink the Log files?

    Should i need to make a maintenance plan to take the DB backup first and then to shrinking?

    Please let me know the best way to automate the log file shrinking.

    No, never do that. Shrinking (Either Data or Log file) is never a best practice. It's a resource-intensive operation and you'll have a very hard time performing a shrink on a busy prod server. That said, the only case where shrinking a log file might be suitable is when a runaway query grows the log file uncontrollably or, your log has a very large number of VLFs (virtual log files), in which case you can perform a one-time shrink to bring it back to a normal state. But never make it a scheduled automated activity. 

    You should instead concentrate on what is causing the log to build up in the first place. Maybe check "log_reuse_wait_desc" column of sys.databases view and see what is holding up the log truncation. Shrinking a log will cause subsequent transactions to grow it again anyway, causing the log growth to stall until the log is zero-initialized. This could be a big deal in the case of a heavy prod load. 

    Oh, by the way, yours is an AG environment which makes the shrink even difficult. Since a shrink normally needs the log to be cleared first (truncate) before it can shrink and if the secondary doesn't catch up with the log records, the log on the primary cannot be truncated until secondary catches up with primary thereby, making it very difficult for you to shrink the log on the primary. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Monday, February 11, 2019 12:59 PM