Error message :
The transaction log for database ‘Database’ is full due to ‘AVAILABILITY_REPLICA’
Error: 9002, Severity: 17, State: 9.
Cause : This happens when transaction modifications made on the primary replica have not yet been replicated on the secondary copy.
Troubleshoot the log file full Issue
When the log file grows, the circumstances below show the log reuse wait desc column as AVAILABILITY REPLICA.
Some of the Latencies that may occur when transmitting recorded updates to secondary replicas
Whatever transaction changes occur on the primary replica are enclosed in log record blocks, which are then sent and hardened to the database log file on the secondary replica. The primary replica will not be able to rewrite these log blocks into its own log file until the blocks have been sent and hardened to the secondary replica’s database log file.
If these blocks are not hardened to any secondary replica in the Availability group in a timely manner, the logged changes in the database at the primary replica will not be truncated, causing the log file to grow.
A committed redo thread in the secondary replica instance applies the contained log records to the corresponding data file after logged blocks have been hardened to the secondary database log file. Until all redo threads on each secondary replica have applied the included log records, the primary replica cannot overwrite these log blocks into its own log file.
If the redo operation on any secondary replica is unable to keep up with the rate at which log blocks are toughened on the secondary replica, the primary replica’s log file will increase. Only up to the point where all secondary replica redo threads have applied can the primary replica truncate and reuse its own transaction log.
If there are several secondary replicas, we can use the DMV sys.dm hadr database replica states to compare the column Transaction lsn to determine which secondary replica database log truncation is delayed.
After determining which secondary database is causing the problem, we can try the following approaches to temporarily solve the problem:
- Remove the Availability group database from the secondary database and re-join..
- Set the Readable Secondary option in the Availability Group properties to NO if the redo thread is regularly blocked. Change the Readable Secondary option to YES if the redo queue has shrunk significantly.
- If Auto grow is deactivated, enable it and make sure you have enough storage space.