Maintaining Log File when CDC is Enabled

When CDC is Enabled, CDC captures and stores changes made to the data in the database, which can result in a large amount of data being written to the transaction log. Additionally, CDC uses a separate table in "cdc" schema to store the change history, which can also contribute to the growth of the log file.

The Main Cause for Log Growth happens when there are lot of transactions that needs to be captured by CDC and till then the VLFs will not be released when Capture Job is not running properly.

CDC creates two SQL Server Agent jobs for each enabled database:

cdc.SOADB_capture: The “Capture” job captures DML changes made to a specific table(s) and stores them in corresponding CDC tables. The job runs at regular intervals to ensure the CDC tables are up to date with the latest changes.

cdc.SOADB_cleanup: The "Cleanup" job removes the CDC data that is no longer needed, such as data that has already been processed or data that has reached a certain age.

You can verify that these jobs are running successfully by checking the job history in SQL Server Agent.

If CDC Jobs are not running properly, then we need to rectify the failures in the Job so that all the changes will be stored in cdc tables. If not, then we need to disable the cdc and then do the regular log maintenance.