0

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?

Improve this question
1
  • 3
    Why are you suspending synchronization to secondary before backing up the transaction log? The log backup also has to write to the transaction log, and that has to be synchronized to secondary, too.
    – AMtwo
    Commented Oct 11, 2020 at 7:20

1 Answer 1

Reset to default
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.