Fixing Data Validation Errors in Replication

checksumLast week, in my previous post I talked about the Data Validation and explained what it means. I talked about Data Validation by Row Count and Checksum in brief. This post is an extension of last week’s article and will take it further on how to fix replication error.

So far, from my experience, I have seen that the data validation errors are reported for a mismatch in row count for the table(s).  And, it is fairly easy to determine the error and fix when it comes to resolving it. Without wasting much time, let’s talk about various ways to deal with this very error.

Fixing Data Validation Errors in Replication:

  • Skip missing Transaction
  • Ignore Data Consistency Issue
  • Drop and Republish articles which are out of sync

Basically, when one has data validation errors in replication it is about inconsistency or out of sync articles. First, one has to find out what exactly the error is. Based on the error reported, use the tablediff utility to compare the records and find out the mismatch records, this utility will also create tsql statement that can help fix the data validation. Row count can be manually validated as well in case the article is small. Once a difference is found, manually insert the record(s) and it will fix the issue.

When a validation or inconsistency reported due to the key violation of duplicate value, find out the offending records. And then, one can create another agent profile and use skiperrors parameter to resolve this error. Also, one can also drop and republish the article that has reported an error and fix the issue.

I hope this tips will help you fix the data validation issue. You may also want to browse through the HA&DR category of this blog space to find out some more tips and tricks.

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.