Transaction log for Availability database is full due to AVAILABILITY_REPLICA

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.

Redo Latency

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.

WorkAround

After determining which secondary database is causing the problem, we can try the following approaches to temporarily solve the problem:

  1. Remove the Availability group database from the secondary database and re-join..
  2. 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.
  3. If Auto grow is deactivated, enable it and make sure you have enough storage space.

Author: Sri

Hello Friends, This blog is to help the IT professionals who want to become a professional SQL Server DBA but don't know how and from where to start with. So, I am going to share my experiences and my learning in this blog. Will talk about what are the pre-requisite skills required to become a Professional SQL Server DBA, how much time it takes to be a good DBA and what are the additional skill sets are required to become a good DBA. Apart from that I will also post real time sql server settings on server level and database level, Configuring High Availability. Also will share the Client requirements with real time setups if possible I will provide you with screenshots.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.