Transaction Log File Considerations when using Change Data Capture(CDC),database log growth due to CDC
One of the most important things to watch out for with change data
capture(CDC) is the transaction log I/O subsystem. As stated earlier, log
file I/O significantly grows when change data capture is enabled in a
database. In addition to that, log records stay active until change data
capture has processed them. This means that especially in environments
where a large latency builds up, the log file can grow significantly
because the log space cannot be reused as long as the change data
capture scan job has not processed the log records, even in simple
recovery model, or even after a log backup in full recovery model.
It should be noted that change data capture works with all recovery models. But when change data capture is enabled, operations that would normally be minimally logged in simple or bulk-logged recovery models are fully logged to enable change data capture to capture all changes.
Be aware also that when a log disk becomes full, you cannot shrink the log file by backing it up and manually shrinking it until change data capture has processed all transactions. But change data capture cannot process the transactions when the log disk is full, because change data capture writes to change tables are logged operations. In this case, the easiest way to recover from this situation is to temporarily add another log file on a different disk.
Information extracted from:https://blogs.msdn.microsoft.com/repltalk/2010/07/19/transaction-log-file-considerations-when-using-change-data-capture/
It should be noted that change data capture works with all recovery models. But when change data capture is enabled, operations that would normally be minimally logged in simple or bulk-logged recovery models are fully logged to enable change data capture to capture all changes.
Be aware also that when a log disk becomes full, you cannot shrink the log file by backing it up and manually shrinking it until change data capture has processed all transactions. But change data capture cannot process the transactions when the log disk is full, because change data capture writes to change tables are logged operations. In this case, the easiest way to recover from this situation is to temporarily add another log file on a different disk.
Information extracted from:https://blogs.msdn.microsoft.com/repltalk/2010/07/19/transaction-log-file-considerations-when-using-change-data-capture/
One day in one of my environments where Replication is configured on "Always On" we could see an issue where log file of database(CDC enabled on this database) has been increased drastically and we are unable to shrink the log file of database due undistributed oldest transaction.
I followed the below steps that are mentioned in the below link. Though the subject line of below link says "SQL Transaction log grows when you use Change Data Capture for Oracle by Attunity". The same work around applicable to SQL Server also. Please follow the steps mentioned in the below link.
Comments