Hi All,
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.
Before making anything we have to trouble shoot the issue carefully, where is the problem. by.
MSreplErrors
MSlogreader_history
MSdistribution_history
MSdistribution_history
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)..
HTH
Vinay
.
