none
SQL Server - Log file not shrinking in AlwaysOn group RRS feed

  • Question

  • We have three-node AO setup in SQL Server. Primary node, one secondary node in Sync mode and another secondary node in Async mode.
    Main Database on Primary node, log file-size is 434GB, Used 99.91%. Log backups is happening every hour only on Primary node.

    Ran following query on Primary and Secondary servers.
    SELECT log_reuse_wait_desc
    FROM sys.databases
    WHERE name = 'Main DB Name'

    Primary node
    log_reuse_wait_desc
    ------------------------------------------------------------
    AVAILABILITY_REPLICA

    Secondary-1 node (Sync mode)
    log_reuse_wait_desc
    ------------------------------------------------------------
    LOG_BACKUP

    Secondary-2 node (Async mode)
    log_reuse_wait_desc
    ------------------------------------------------------------
    AVAILABILITY_REPLICA

    While other DBs (much smaller than main DB) in AO groups log usage is very minimum (1 to 10%) and can be shrunk, main DB log usage continuously showing more than 99% and not shrinking.

    I am guessing since log_reuse_wait_desc is showing "AVAILABILITY_REPLICA" on Primary Node, means not all the log is transferred to Secondary replica, and so its still full.

    I checked Redo Queue Size in Secondary-1 Node showing 27GB, Secondary-2 is showing nothing. So most likely Secondary 1needs to get all logs from Primary. The problem is Redo Queue Size in Secondary-1 Node is increasing and not decreasing as what I through it should. Also log-file on Primary node is slowly increasing which is going to fill-up whole disk-space.

    Now Secondary-1 Node showing LOG-BACKUP. How do I solve this issue? Should I take Log backup on Secondary-1 Node and check?
    Tuesday, August 20, 2019 5:53 AM

All replies

  • Hi spark53,

    >> Now Secondary-1 Node showing LOG-BACKUP. How do I solve this issue? Should I take Log backup on Secondary-1 Node and check?

    It seems only backup log could not solve the issue. The log_reuse_wait_desc is showing "AVAILABILITY_REPLICA" on Primary Node, means your primary replica is waiting for a secondary replica to complete the processing of log records for a REDO. And you confirmed this by checking Redo Queue Size in Secondary-1 Node. But the Redo Queue Size in Secondary-1 Node is increasing and not decreasing. You may need to check the values of AG state, Synchronization State, Suspended, Suspend Reason, Log Send Queue Size (KB), Log Send Rate (KB/sec), Redo Queue Size (KB), Redo Rate (KB/sec) from Always On Availability Group dashboard. You may find the reason there. You can also share the result with us.

    Please also check whether below link could help you. When A Log Backup Does Not Truncate Your SQL Server Log Files In An Availability Group

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, August 21, 2019 6:13 AM