SQL SERVER – How to Fix log_reuse_wait_desc – AVAILABILITY_REPLICA?

Earlier this week I had multiple engagements of Comprehensive Database Performance Health Check. While working with one of the customers we realized that when we query sys.databases for one of the databases, we always see column log_reuse_wait_desc containing reasons  AVAILABILITY_REPLICA. The customer was really curious about what it means. Let us see what we found out after quick research.

SQL SERVER - How to Fix log_reuse_wait_desc - AVAILABILITY_REPLICA? AlwaysOn

First of all, run the following script

SELECT name, log_reuse_wait_desc
FROM sys.databases

Above script will return the name of the database along with the log_reuse_wait_desc. The column displays the reason of transaction log space is currently waiting on to clear. If you see it containing AVAILABILITY_REPLICA, that means an AlwaysOn Availability Groups secondary replica is applying transaction log records of this database to a corresponding secondary database. In another word, your secondary database is being synced at that moment.

There can be multiple reasons for why this particular wait type may show up. Let me quickly list all the reasons here:

Reason 1: Slow Network or Unreachable Network

In this case, you may want to check with your network admin and check the reasons for the slow network. One of my customers had incorrectly configured network card once was slowing down the syncing.

Reason 2: Long Running Transactions

Sometimes it is quite possible you will see both the primary and secondary database both synced but still you will see the log_reuse_wait_desc displaying AVAILABILITY_REPLICA. In that case, it is quite possible that on the primary server there is a long-running transaction, which is currently actively writing in the log file which is yet not shipped to secondary.

Reason 3: Resource Constraint

In a rare incident, it is quite possible that sometimes on the secondary we run out of the worker thread. In that case, you can either restart the sync or if that works, you may have to reset the sync from the beginning.

Well, so far I have found realized that there can be any one of the reasons listed above can be the cause of the AVAILABILITY_REPLICA as a log_reuse_wait_desc. Let me know if you find out any other reasons and solutions for the same.

Reference: Pinal Dave (https://blog.SQLAuthority.com)

AlwaysOn, SQL Replication, SQL Scripts, SQL Server, Transaction Log
Previous Post
SQL SERVER – Identifying Query Growing TempDB
Next Post
SQL SERVER – Remove Duplicate Rows Using UNION Operator

Related Posts

5 Comments. Leave new

  • @Pinal, just for additional information to this article. Not only that, If there is a secondary replica disconnected or not in sync either it will be in async or sync mode will give the same problem.

  • you may be running the indexes.. that might blow up your log files…when log files are large – AG is going to fall behind… take appropriate actions.

  • what can be done if log files are filling and become large

  • Pinal, would it be possible to expand this article? Eg) you write “it is quite possible that sometimes on the secondary we run out of the worker thread” – how to check for that? You also wrote “you can either restart the sync or if that works, you may have to reset the sync from the beginning” – I’m confused – what’s the difference between the two options you’re describing here? For your options 1 and 2 – would these scenarios show on the AG dashboard – for example, having a large redo queue or wait time? Maybe more information on how to confirm those scenarios too.


Leave a Reply