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

  • Question

  • We have three-node AO group 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 9:46 AM

All replies

  • Taking log backups will not help. Check to see how far behind you are in the queue.

    Look at the queue sizes and make sure everything is healthy, use the sys.sys.dm_hadr_database_replica_states for this. Check the values for synchronization_state_desc,  synchronization_health_desc, and suspend_reason_desc.

    If you did an index or imported a large number of rows on your primary you can get quite far behind.



    Tuesday, August 20, 2019 1:40 PM
  • Hi Robin,

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

    As Hilary mentioned, 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:16 AM