The 'SkipErrors' parameter for the Replication Distribution Agent

Having encountered numerous errors in SQL Server Replication, I can confidently say that the majority occur at the replication agent level, such as with the Distribution Agent, Log Reader Agent, Snapshot Agent, Queue Reader Agent, and others. Unfortunately, many of these issues are related to Primary and Foreign Key conflicts, which can take significant time to resolve, either individually or by reconfiguring the replication. To avoid downtime and allow operations to continue while we work on fixing these errors, we can use a helpful option: the 'SkipErrors' parameter. I've successfully used this parameter many times to bypass such issues, and it can also be applied to skip other types of errors as needed.

 
Today's post will show how to use the' SkipErrors' parameter which allows to skip specific errors so that the data synchronization process is not stopped. This parameter is configurable in the profile of the Distribution Agent and has as input the error number we may want to skip.

 
The following picture shows an error (with code 547 related to an Foreign Key issue) in the Distribution Agent process and we see how the transactions are being queued due to this error, consequently, there is a need to fix it so as to allow the rest of pending transactions are moved on. (Distribution Agent reads sequentially the table 'msrepl_commands' to get the command to execute in the subscribers, this means that First in the Queue is the First Out to be moved to subscribers.)



Other common errors where you can use 'SkipErrors' parameter is when there are not some rows in the subscriber to apply the changes (The row was not found at the Subscriber when applying the replicated command). The error code for this case is 20598.
 

I mentioned before that the 'SkipErrors' parameter is configurable inside the Distribution Agent profile and that is what we are going to do right now. Firstly, we need to create a customized profile based on the Default profile and write in the 'Value' column the numbers of the errors (to be skipped)  separated by colons as we can see in the following picture 20598:547.



Having done that, we may have to restart the Distribution Agent. Next time the Distribution Agent starts up, it will load the new customized profile with the error codes to be skipped (ignored). Finally, not only will we verify that many errors are skipped, but also the Distribution Agent is running with no problem.
 

Just to finish writing this post, keep in mind that the transactions with these errors were missed, which means that you will not be able to recover them and may affect your data consistency. In other words, we must use 'SkipErrors' parameter with extreme caution. Let me know any remark you may have. Thanks for reading.