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

Solarwinds
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)

Solarwinds
, , , ,
Previous Post
SQL SERVER – Identifying Query Growing TempDB
Next Post
SQL SERVER – Remove Duplicate Rows Using UNION Operator

Related Posts

Leave a Reply

Menu