"Error 9002. The transaction log for database is full due to 'AVAILABILITY_REPLICA'" error message in SQL Server 2012

Applies to: SQL Server 2012 Enterprise

Symptoms


Consider the following scenario:
  • You have Microsoft SQL Server 2012 installed on a server.
  • The instance of SQL Server is in an AwaysOn Availability Groups environment.
  • The autogrow option for transaction log files is set in SQL Server.
In this scenario, the transaction log may become very large and run out of space or may become full. Therefore, you receive an error message that resembles the following:

Error: 9002, Severity: 17, State: 9.
The transaction log for database '%.*ls' is full due to 'AVAILABILITY_REPLICA'

Note
 If the autogrow option is set in SQL Server 2012, transaction log files usually can expand automatically up to the maximum size of the log file.

Cause


The "9002 error" message indicates that the log is full because of the availability replica. This indicates that the logged changes in the availability database at the primary replica that have not arrived and that these changes were applied to the availability database at one of the secondary replicas. Until logged changes arrive and are applied, the changes cannot be truncated from the availability database log at the primary replica.

Troubleshooting


"Log Send Queue" and "Redo Queue" are measurable data points during availability database synchronization. You can monitor these data points in order to determine whether an availability database log cannot be truncated because of the log uses type AVAILABILITY_REPLICA.
  • Log Send Queue
    When a transaction is performed at the primary, the logged blocks must be delivered and hardened to the database log file at the secondary. Any delay will prevent truncation of those logged changes in the database at the primary replica. A common reasons for a sustained log send queue is latency in the network or during hardening (write to disk) of the log blocks on the secondary.
  • Redo Queue
    As soon as it is hardened to the secondary database log file, a dedicated redo thread applies the log records. If the redo operation cannot keep up with the transaction log that is generated, log growth may occur. If the secondary replica redo operation is behind in applying those changes to a corresponding secondary database, the primary will truncate the transaction log.


Workaround


After you identify the secondary database that makes this occur, try one or more of the following methods to work around this issue temporarily:
  • Take the database out of the availability group for the offending secondary.

    Note This method will result in the loss of the High Availability/Disaster Recovery scenario for the secondary. You may have to set up the Availability Group again in the future.
  • Add more log space or log files.

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

More Information


Transaction log expansion may occur for one of the following reasons: 
  • A very large transaction log file may exist. 
  • Transactions may fail and may start to roll back.
  • Transactions may take a long time to finish. 
  • Performance issues may occur.
  • Blocking may occur.

For more information about why a transaction log grows unexpectedly or becomes full in SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:
317375 A transaction log grows unexpectedly or becomes full in SQL Server

For more information about the Redo operation blocking problem, go to the following Microsoft Developer Network (MSDN) blog:
For more information about AVAILABILITY_REPLICA-based log_reuse_wait columns, go to the following MSDN website:
For more information about the sys.dm_hadr_database_replica_states view, go to the following Microsoft TechNet website:

For more information about how to monitor and troubleshoot logged changes that are not arriving and are not being applied in a timely manner, go to the following TechNet website: