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.
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)
6 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
Take backup.
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.
other reason is because the secondary replica have running a backup
bye