SQL Server - Log file not shrinking in AlwaysOn group

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'

Result:

node        log_reuse_wait_desc
------------------------------------------------------------
primary     AVAILABILITY_REPLICA
Secondary-1 LOG_BACKUP
Secondary-2 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?

Answer 1

The issue is resolved. Apparently one of programs connecting to same large DB had been running for more than 24-hours, seemed it was in "hung" state, had several open connections to secondary replica-DB caused blocking. I guess that was causing not completing log replication from primary to secondary, because when I ended those programs, immediately blocking were gone and Redo Queue Size started decreasing instead of previously increasing. After about an hour or so, log file in primary node showed more than 80% available free space.

dbcc opentran and sp_who2 didn't give that much details to track this issue. What helped was

select * from sys.sysprocesses where blocked<>0
dbcc inputbuffer(spid)

The first statement was to find out what was causing blocks, and second one to kill. I am just sharing if it helps others in similar situation.

TLog backup is being taken for all user-Databases in every hour only on primary node as mentioned in my post. So far TLog physical file-size was small, wouldn't increase and most of the time had more than 90% available free space - which is what its supposed to be. So after the issue is resolved, only difference is file-size is very big which I understand because it kept on growing. Now I need to do a TLog shrink (we did before) and it should go back to initial size.

TLog back doesn't need to be taken on all replicas. Technically you can, but if you are taking on all replicas, in case of Database restoration you have to gather all TLog backups from all Nodes and restore them which may be complicated.

Answer 2

Tacking log backup on Secondary-1 might or might not resolve the issue:

I checked Redo Queue Size in Secondary-1 Node showing 27GB, Secondary-2 is showing nothing. So most likely Secondary 1 needs 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.

Cause:
The continues increase of log could be due long running select query on Secondary Replica which blocks the DDL operations that are send by Primary replica. This result secondary replica back-logged and redo queue increase.

Identification:
In this case, we can verify the redo blocking at Secondary-1 via Extended events -> Sessions -> AlwaysOn_health.

Solution:
Once redo blocking identified via extended events, performing KILL on particular select operation (at secondary) replica would release redo blocking and eventually Back-log (redo_queue_size) would be cleared. For detailed steps on similar case study and fix..

If the blocking not appeared at Extended Events of secondary replica, consider WAIT_TYPES particularly with REDO thread, For detailed steps.

---

Following steps might help as quick fix to stop further growth of the log in Secondary-1 Replica.

1. Suspend particular database synchronization on Secondary-1 Replica, wait untill the long query completed on Primary replica, then Resume synchronization on Secondary-1 Replica. you can do this via
   SSMS -> availability groups (Group Name) -> availability databases

---

If the problem remain same, you can proceed with following steps:

Remove and re-join secondary database without interrupting other databases in same AG. (Consider there is impact if read-only routing configuration in-place:)

1. Remove Secondary database on Secondary-1 Replica
   Using SSMS -> availability groups (Group Name) -> availability databases (right click on DB). or

  -- On SECONDARY 1
   ALTER DATABASE DbName SET HADR OFF;

By doing this Secondary-1 Replica database becomes Restoring mode but no impact on AG or Primary replica

2. Restore recent backups Full and Log on Secondary-1 Replica, do not forget to select RESTORE WITH NORECOVERY
3. Once restoration completes, Join the database via Secondary-1 Replica
   Using SSMS -> availability groups (Group Name) -> availability databases (right click on DB) or

   -- On SECONDARY 1
   ALTER DATABASE DbName SET HADR AVAILABILITY GROUP = AG_NAME;