Search
Close this search box.

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 *

Picture of Hemantgiri Goswami

Hemantgiri Goswami

Throughout my extensive 24-year tenure in the IT industry, I have honed my expertise in SQL Server and cloud technologies. My qualifications include certifications in ITIL, Azure, and Google Cloud, and my professional journey boasts a consistent record of delivering top-notch, dependable, and efficient solutions across diverse clients and domains. In recognition of my dedication and impact, I am honored to have received the Microsoft MVP award for SQL Server on six occasions. Additionally, I actively contribute to various online forums and blogs, acting as a moderator and facilitator of meaningful discussions. My ultimate mission revolves around empowering organizations to enhance the reliability and efficiency of their SQL Server implementations while fostering a culture of continuous learning and growth within the SQL Server community.