As you know Change Data Capture harvests transactions from the log file. I was wondering what happens when the log file gets truncated during transaction log backups or when an automatic checkpoint takes place on a database in simple recovery mode causing log truncation. What if the CDC job has not harvested those records in the portion of the log to be truncated. I had the feeling that the SQL Engine ensured that this would not happen but I wanted to understand the internals of this process. turns out, with all the information available about CDC it was hard to find a straight answer on this. But I did.
Change Data Capture uses the same stored procedure and logic that replication uses behind the covers. If the database where CDC is being enabled has an object published through transactional replication the same log reader agent takes care of harvesting the changes from the transaction log for both replication and CDC. Otherwise a job is created to perform this task. The key is that either way the same stored procedure is used to read the transaction log (sp_replcmds ) and all the verifications that take place to ensure that the transaction log is not truncated unless the changes in the inactive portion of the log have been harvested by sp_replcmds are followed just as they are with transactional replication.
This is something to keep in mind since now you have another variable that can affect the size of your log files. If your transaction logs are not been truncated and CDC is enable in your database, go ahead and check the CDC related jobs to make sure that they have been running successfully and often enough.
I have to say, I read several pages in BOL but only one article gave me a straight answer to this question.
Thank you Paul Randal for your article!! "Tracking Changes in your Enterprise Database"
Everything else you need to know about Change Data Capture is here: http://msdn.microsoft.com/en-us/library/bb522489.aspx
No comments:
Post a Comment