How does CDC impact the transactional logs?

I got case that the customer has issue with transaction log in Managed Instance, basically the transactional log increases to 500 Gb! When they checked with sys.log_reuse_wait_desc they found it was waiting on replication but their first question was we have not replication on database.

If you’re experience the same problem and you already enabled CDC might be this post help you!

Microsoft says the following values of log_reuse_wait_desc in sys.databases may indicate the reason why the database’s transaction log truncation is being prevented.

So, if you enabled the CDC and see “Replication” in the result of the query it explains why.

Why CDC can impact the transaction log?

For answering this question, I also need to refer you to this document, please be aware Change data capture doesn’t do anything as part of the transactions that change the table being tracked. Instead, the insert, update, and delete operations are written to the transaction log. Data that is deposited in change tables will grow unmanageably if you do not periodically and systematically prune the data.

Find CDC jobs and monitor them

If you enable CDC in your database, you need to have some capture and clean-up jobs and monitor the jobs frequently. Those jobs are created when the first table in the database is enabled for change data capture.

If for any reason you want to define capture and clean up job you need to run the following command. See the referral link

EXEC sys.sp_cdc_help_jobs;  
GO   

In this case we found the clean-up job for some reason has failed in last few days and that why the transaction log did not truncate.