Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1517
  • Last Modified:

SQL Transaction Log Truncation when CDC is already applied.

I have a db that i have applied CDC on certain tables. On a client's request i have to truncate SQL server transaction log for that DB. Can any one tells me what would happen to my captured change data. Does SQL server persis that change data in captured tables. What would be the impact on captured table as it contains LSNs. What would happen to cleanup job as it also require LSN from captured table ?

 Please answer asap..

  • 2
1 Solution
Scott PletcherSenior DBACommented:
CDC has its own tables, it doesn't rely on the "live" log.  Thus, you can backup the log w/o affecting CDC, after which it can be overwritten.  

I guess you can explicitly truncate the log as well, although this is less clear.  When you backup the log, SQL "knows" it can then be overwritten so SQL "knows" to save any log info it needs before marking the backed-up areas of the log as unused / available to be overwritten.  SQL doesn't ever "expect" you to truncate the log, so there likely won't be any internal check to verify that CDC has everything it needs before truncating vs backing up.

My best guess would be that if a reasonable period of time has elapsed since the last mod to the table, and a checkpoint has been done, the log truncate would not lose any data from CDC.
codedeveloper1Author Commented:
Thanks Scott for your comment:

Let me tell you a little background. I have to propose CDC as complete solution of auditing our application and the showstoppers could be
1. that if a transaction log is truncated then what will happen to change data that is already captured. What you are saying that data in  _$start_lsn and _$end_lsn does not refer to live transactional log. Data will persist in captured columns. What about the cleanup job it also requires those lsn columns , does the cleanup job also does not require live log data?

2. 2nd show stopper could be. If a column is added to a table which already has CDC applied? will SQL server automatically capture data for that column or i want to capture that new column data in the same captured instance.

Please answer both points thanks...
Scott PletcherSenior DBACommented:
>> What about the cleanup job it also requires those lsn columns , does the cleanup job also does not require live log data? <<

SQL will make sure the log records are not marked as available for reuse until all needed CDC data has been captured.  Once the data has been captured [CDC has its own change tables that it uses], the log can be reused normally.

>> 2. <<
If you add a column/s, CDC will ignore that/those column/s by default.
But you can explicitly create a second CDC for the same table that includes new column(s) (and any additional old columns you want/need).  

But you cannot ever have more than 2 CDC change captures in effect for the same table at the same time.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now