How to fix replication issue cannot truncate table

I have the privilege to work on Saturday and Sunday, holding a pager during my shift hours. I get most of the blog ideas during this stint. Today’s post is no exception. In the month of  October while working on one of the weekends I have the pager alert that reads like the one below.

cannot truncate table SomeTble_Subscriptions because it is published for replication or enabled for Change Data Capture.

 

 

It was past midnight for my client so I was the one who has to decide the next course of action, I don’t even need to inform him. Of course, at these hours of night, he is not going to read and reply to emails. I’ve logged in to the server and spent some time reading logs, job history, replication logs before and after the said error was reported.

How did I fix the error cannot truncate table SomeTble_Subscriptions because it is published for replication or enabled for Change Data Capture.

Step 1. Determine the name of the subscriber

Step 2. Open up the shared location where the snapshot files were created

Step 3. Open up the files with the extension .pre and .sc

Step 4. Modified the first statement itself

Step 5. Replace the TRUNCATE Table statement with DELETE From

Step 6. You are done! This has fixed the issue and the replication has started working fine again

Note: When you read the SQL Error logs, job history, or Replication Logs you will also notice that it would report Error: 14151, Severity: 18, State: 1. as well

I believe you are going to get some help out of this post. In case you are interested to learn some more tips on troubleshooting replication issues please browse through this link.