Continue on Transaction replication discussion:
Today I would like to share some of the important ways of handling/troubleshooting replication issues which i come across and learned during my work on Transactional replication.
There are some common issues(errors) we get and replication fails. some of them which I know are as follows:
1.Violation of PRIMARY KEY constraint .<primary key>….Cannot insert duplicate key in object <objectnm>….
2.DELETE statement conflicted with COLUMN REFERENCE.
3. Time out at log reader agent.
4. Time out at Distributor agent.
5. log reader failed with The process could not execute ‘sp_repldone/sp_replcounters’
Before making anything we have to trouble shoot the issue carefully, where is the problem. by.
and properties of Replication Monitor… and try to understand the error log.
The issue 1 and 2 are related with the data. where someone has insert(enter) data into subscriber and when we insert the same value into publisher it will fail with error as data is already their on subscriber.
These issue we can resolve in two ways(no way its customer education issue 🙂 ):
a. store allowing anyone to enter data into subscriber.
b. for correction of issue temporary remove the replications which are failed from distribution.
check the problematic xact_seqno (remove trailing 0000000) from msrepl_commands and msrepl_transactions and delete them from it. means the changes will be done at publisher but before going to subsriber we removed it(as this case subsriber is already having data). or we can skiperror and continue.
The issues 3 and 4 are more related with the replication latency. due to huge transaction on logging and distributing this could happen. the quickest way to resolve this is to increase the query time from the Logreader and distributor agent properties(by creating new profile)..
5. Today when I saw on my devlopment logreader failed with error 5. I donot remeber what I was working on but as it was a development and no transaction we pending to transfer and both publisher and subsriber are in sycn.
so I was safe to run
and my log reader started working…. but Checked on BOL for the command it shows only run this command while doing backup restore of replication databases.
And to improve the performance should read this blog: