log_reuse_wait_desc – AVAILABILITY_REPLICA Unable to truncate transaction log

Posted on

Question :

I have just 1 secondary replica and just one database in my availability group. Both Primary and Secondary replicas are in synchronous-commit availability mode. I inserted 1000 records into the table and transaction was committed in both primary and secondary databases. Checkpoints were also issued. Now when I suspend the secondary database and try to take log backup of my primary database in order to truncate transaction log, it won’t be truncated with the following log_reuse_wait_desc value: AVAILABILITY_REPLICA. Why isn’t it truncated?

As far as I know as long as logs are inactive and VLF is filled up then transaction log can be truncated. Some of you can answer it in a way: SQL Server is waiting to send logs to the secondary Always On Availability Group replica, but my question is if 1000 records were already committed on both sides and checkpoints were issued, what logs are then waiting to be sent to the secondary replica?

Answer :

The reason you are seeing this behaviour is due to the fact that you have SUSPENDED the data synchronization (see @AMtwo’s comment).

From BOL,

a secondary replica must be able to communicate with the primary replica and must be SYNCHRONIZED or SYNCHRONIZING.

so if you unsuspend the data synchronization, then the log can be reused since you took the log backup.

Leave a Reply

Your email address will not be published. Required fields are marked *