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.