SQL Transaction Log Truncation when CDC is already applied.

Posted on 2012-08-15
Last Modified: 2012-08-16
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..

Question by:codedeveloper1
    LVL 68

    Expert Comment

    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.

    Author Comment

    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...
    LVL 68

    Accepted Solution

    >> 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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now