Replication CDC -- Cannot Truncate Table

  • GonnaCatchIT

    SSCrazy

    Points: 2797

    Dear All,

    We just enabled Replication services (CDC) on few of the databases. Now we realize tables cannot be truncated if they are part of CDC.

    I have below questions, need some views on this.

    1. What are the limitations of using CDC, I know one now - truncate. But what else impact it can have.
    2.  Is there a way to allow truncate & also enable it for CDC. Currently I removed the table from CDC process.

    Thanks!

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    just for clarification (before I give you the wrong advice) - You mentioned Replication and CDC (change data capture)

    which one are you using? they are very different things

    MVDBA

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    additionally were you running previously in simple recovery mode?  the database may have been changed to full recovery and there is no t-log backup job..... just guessing.

    you could also try a checkpoint command

    MVDBA

  • anthony.green

    SSC Guru

    Points: 112401

    A table in replication cannot be truncated, due to truncate being a minimal logged operation and this the full delete pages not logged in a fully logged operation so CDC or replication doesn't know how to handle it.

    select * from sys.messages where text like '%change data capture%' and language_id = 1033

    The above will spit out the messages / errors pertaining to CDC, some noticeable ones, you can't use column sets, cant truncate the log as the log reader isn't running etc.

    Take a look through them to see what warnings/errors can come out from SQL relating to CDC.

     

    But my question is why would you want to truncate a CDC enabled table, you're not getting the delete history, do you actually need CDC on it?

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    apologies - I read "truncate table" as "truncate log" my mistake

    MVDBA

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply