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.

One Response

  1. Hello there! This article couldn’t be written any better! Looking at this post reminds me of my previous roommate! He continually kept preaching about this. I’ll forward this information to him. Fairly certain he’ll have a good read. Thank you for sharing!

Leave a Reply

Your email address will not be published. Required fields are marked *

Hemantgiri Goswami

Hemantgiri Goswami

Hemantgiri is a seasoned SQL Server Consultant with demonstrated history for close to 21 years. He is a published author specializing in High Availability and Disaster Recovery area. He was awarded Most Valuable Professional by Microsoft 4 times. He is a regular speaker at events in Surat. Hemantgiri is founder and leader of the SQLPASS Chapter for Surat.