0

Once per week, a snapshot publication is done from a database "myDB" in PROD to "myDB" in PREPROD.

Now in PREPROD we set up a transactional replication between "myDB" to another PREPROD database "myDB2".

The problem is that the transactional replication block the SNAPSHOT with the error :

Cannot truncate table 'dbo.myTable' because it is published for replication or enabled for Change Data Capture

The solution I think is deleting the transactional replication once a week, creating a snapshot replication, run it, then delete it. After that recreate the transactional replication. And doing that by scheduling scripts containing the publications/subscription .

Is there a cleaner solution to achieve that ?

1 Answer 1

0

This is likely because the Articles that are used in both your Snapshot and Transactional Publications have their Destination Object properties set to "Truncate all data in the existing object" for the Action if name is in use property:

Replication Article Properties

Truncate is not allowed on tables that are bound by certain things, such as another Publication.

Instead, you can change it to the option "Delete data. If article has a row filter, delete only data that matches that filter". I believe you should only have to do this on the Snapshot Publication side (but if that doesn't work, try doing it on the Transactional Publication side too). There's also another option called "Drop existing object and create a new one", but I believe you'll run into a similar error with your current setup, so "Delete..." is probably the only option that'll work for you.

5
  • Although the DELETE will be logged and replicated, as will the subsequent loads. So it may be worth dropping and recreating the transactional replication in pre-prod. Aug 2 at 13:45
  • @DavidBrowne-Microsoft not sure if I fully follow. Could you please elaborate what you mean by that? (I'm thinking you mean the reinitialization of the Snapshot Publication will create a lot of work for the Transactional Publication?)
    – J.D.
    Aug 2 at 17:41
  • 1
    If the snapshot agent deletes 1M rows and then inserts 1M rows, then 1M deletes, followed by 1M inserts will go through the distribution database and be applied at the subscribers. If you drop replication, truncate, and bulk load the republisher, and then recreate the replication, the republisher's snapshot agent will BCP out the tables and bulk load then into the subscribers. So the data flows through the snapshot files, rather than the distribution database. Aug 2 at 17:46
  • @DavidBrowne-Microsoft Gotcha, thanks!
    – J.D.
    Aug 2 at 18:03
  • Thanks for this answer. I just found this question stackoverflow.com/questions/70871824/…, it is working on my local database and avoid me to delete / re create the replication.
    – jpprade
    Aug 3 at 12:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.