Error message :
The transaction log for database ‘Database’ is full due to ‘AVAILABILITY_REPLICA’
Error: 9002, Severity: 17, State: 9.
Cause : This occurs whatever the transaction changes happened at primary replica are not yet hardened to the secondary replica.
Troubleshoot the log file full Issue
Below are the scenarios when the log file is to grow and shows the log reuse wait desc column as AVAILABILITY_REPLICA
Some of the Possible Latencies when delivering logged changes to secondary replicas
Whatever the transaction changes happening at primary replica those will be encapsulated to log record blocks and these logged blocks are delivered and hardened to database log file at secondary replica. The Primary replica cannot overwrite these log blocks into its own log file untill the blocks have been delivered and hardened to corresponded database log file at secondary replica.
If there is any delay in hardened of these blocks to any secondary replica in Availability group will prevent truncation of those logged changes in the database at primary replica and will cause the log file to grow
Once logged blocks have been hardened to the secondary database log file, a committed redo thread in the secondary replica instance applies those contained log records to corresponded data file.The primary replica cannot overwrite these log blocks into its own log file untill all redo thread on each secondary replicas have applied the contained log records
if the redo operation on any secondary replica is not able to keep up with the speed at which log blocks are hardened at the secondary replica , this will cause log file to grow at the primary replica. The primary replica can only truncate and reuse its own transaction log up to the point at which that all secondary replica redo threads have applied.
If there is more than one secondary replica we can compare the column Transaction_lsn from the DMV sys.dm_hadr_database_replica_states to get on which secondary replica database log truncation is delaying.
After identifying the secondary database which is causing this issue, we can try the following methods to work around this issue temporarily:
- Take out the Availability group database from secondary and join back again.
- If the redo thread is frequently blocked, set the Readable Secondary option to NO from the properties of Availability Group. Once the redo queue has reduced to an considerable size, change the Readable Secondary option to YES
- Enable the Autogrow setting if it is disabled and make sure to have available disk space.