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/

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

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server