We have a 3 node AAG (below is our setup). Our transaction log file has a growth limit of 60GB. I found out that this error occurred because the CDC failed. I could not start CDC and the transaction log is not truncating.
I checked the log_reuse_wait_desc and it indicated REPLICATION which means that there are committed transactions that haven’t yet been scanned by the transaction replication Log Reader Agent job for the purpose of sending them to the replication distributor or harvesting them for Change Data Capture (which uses the same Agent job as transaction replication). The job could have been disabled, could be broken, or could have had its SQL Agent schedule changed.
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = 'DB_Name'
I also ran the command DBCC OPENTRAN and saw Replicated Transaction Information.
To resolve the issue, I did the following steps.
Create a new transaction log file (it is a good thing that our currently t-log file has a growth limit else it could have occupied all the disk space.)
I started CDC and it ran successfully.
But the t-log was still not truncating. I checked the log_reuse_wait_desc again and it indicated “AVAILABILITY REPLICA” which means that the database mirroring partnership has some latency in it and there are log records on the mirroring principal that haven’t yet been sent to the mirroring mirror (called the send queue). This can happen if the mirror is configured for asynchronous operation, where transactions can commit on the principal before their log records have been sent to the mirror. It can also happen in synchronous mode, if the mirror becomes disconnected or the mirroring session is suspended. The amount of log in the send queue can be equated to the expected amount of data (or work) loss in the event of a crash of the principal.
I waited for few minutes and I checked the log_reuse_wait_desc and now I see “NOTHING” which means that SQL Server thinks there is no problem with log truncation.
