Error from Shrink Transaction Log because of AVAILABILITY_REPLICA

Question

One day, I found a db transaction log file growing so big which is also in AG. I couldn't understand why because full backup and transaction log backup running fine. I took a extreme measure which was to remove the db from AG, switch it to SIMPLE mode and shrink the transaction log file. When I try to shrink the log, I get the error like below. I ran a qeury against sys.databases and found a record with the db and AVAILABILITY_REPLICA. Can you help with shrinking the transaction log without getting the error below?

Error: The log scan number (202388:69632:1) passed to log scan in database is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf).

Answer 1

Hi @Chulhee Kim ,

There are two scenarios that can lead to log growth in an availability database and the 'AVAILABILITY_REPLICA' log_reuse_wait_desc:

•Scenario 1: Latency delivering logged changes to secondary

When transactions change data in the primary replica, these changes are encapsulated into log record blocks and these logged blocks are delivered and hardened to the database log file at the secondary replica. The primary replica cannot overwrite log blocks in its own log file until those log blocks have been delivered and hardened to the corresponding database log file in all secondary replicas. Any delay in the delivery or hardening of these blocks to any replica in the Availability Group will prevent truncation of those logged changes in the database at the primary replica and cause its log file usage to grow.

•Scenario 2: Redo Latency

If the redo operation on any secondary replica is not able to keep up with the speed at which log blocks are hardened at that secondary replica, it will lead to log growth at the primary replica. The primary replica can only truncate and reuse its own transaction log up to the point that all secondary replica's redo threads have applied. If there is more than one secondary, compare the truncation_lsn column of the sys.dm_hadr_database_replica_states dynamic management view across the multiple secondaries to identify which secondary database is delaying log truncation the most.

Corrective measures may include but are not limited to the following:

•Make sure that there is no resource or performance bottleneck at the secondary.
•Make sure that the Redo thread is not blocked at the secondary.

Please refer to MS document to get detail troubleshoot steps and warkaround.

If it is still not work, please check whether the mdf and ldf of this database are corrupt.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Hi @Chulhee Kim ,

This issue is something that I've seen before and I know what can solve it:

I ran a qeury against sys.databases and found a record with the db and AVAILABILITY_REPLICA.

It can be solved by following the steps below:

1. Re-add the database into the AG using MANUAL SEEDING (this should only add it on the primary without affecting the secondary)
2. Suspend (Pause) data movement for the database
3. Remove the database again from the AG

Obviously, in order for the database to be re-added into the AG, you'll have to set its recovery model back to FULL and take a Full + Transaction log backup.

The root cause of such scenarios is often incorrect removal of the database from the AG (i.e. one or more necessary steps were skipped, or some steps were done too quickly before the previous steps were fully completed).

Think of it like physically pulling the plug on a computer instead of properly shutting it down via the "power" menu in Windows... It has risks.

Answer 3

@CathyJi-MSFT Thank you so much for your help. I removed the db from AG, changed its Recovery mode to SIMPLE, and trying to truncate the transactional log in vain. Is there anything I should try for successful log truncation? I will appreciate your help once again.

Answer 4

@Eitan Blumin , Thank you so much for your information. I'm sorry for the late response.

I tried your suggestion. Here's what is happening. I am getting the similar error message when I try to change recovery mode from SIMPLE to Full before adding the db to AG. I just found that that error is also occurring during full backup. Can you help please?

The log scan number (202388:69632:1) passed to log scan in database 'XXXXX' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
ALTER DATABASE statement failed. (.Net SqlClient Data Provider)

Answer 5

Hi @Erland Sommarskog ,

I am encountering an issue before trying to add the db to AG. In order to add the db to AG, the recovery mode has to be Full. The problem is that when I tried to change the recovery mode from SIMPLE to Full, I am getting the error mentioned above.