Error message :
The transaction log for database ‘Database’ is full due to ‘AVAILABILITY_REPLICA’
Error: 9002, Severity: 17, State: 9.
Cause:
The primary replica transaction modifications that have not yet been replicated on the secondary copy are the cause of this.
Troubleshoot the log file full Issue
The parameters below display the log reuse wait desc column as AVAILABILITY REPLICA when the log file grows.
A few of the latencies that could happen when sending recorded updates to secondary replica
Any transaction changes made on the primary replica are contained in log record blocks and transferred to the secondary replica’s database log file, where they are hardened. Until the blocks have been transferred and hardened to the database log file of the secondary replica, the primary replica will not be allowed to rewrite these log blocks into its own log file.
The logged changes in the database at the primary replica will not be truncated if these blocks are not hardened to any secondary replicas in the Availability group in a timely way, which will result in the log file expanding.
Redo Latency
After logged blocks have been moved to the secondary database log file, a committed redo thread in the secondary replica instance applies the contained log records to the appropriate data file. The primary replica cannot replace the provided log records into its own log file until all redo threads on each secondary replica have applied them.
The primary replica’s log file will grow if the redo operation on any secondary replica is unable to keep up with the pace at which log blocks are produced on the secondary replica. The primary replica can only truncate and reuse its own transaction log once all secondary replica redo threads have applied. If there are several secondary replicas, we can compare the column Transaction lsn in the DMV sys.dm HADR database replica states to identify which secondary replica’s database log truncation is delayed.
WorkAround:
We can try the following methods to momentarily fix the issue after identifying which secondary database Server is the issue:
- Unjoin the secondary database and then rejoin after removing the availability group database.
- If the redo thread is frequently blocked, set the Readable Secondary option in the Availability Group setting to NO. In the event that the redo queue has drastically decreased, set the Readable Secondary option to YES.
- If Auto Grow is disabled, turn it on and check that you have enough storage.