February 18, 2020 at 4:55 am
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.
Thanks!
February 18, 2020 at 2:42 pm
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
February 18, 2020 at 2:49 pm
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
February 18, 2020 at 3:08 pm
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?
February 18, 2020 at 3:15 pm
apologies - I read "truncate table" as "truncate log" my mistake
MVDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply