Conflicts from nowhere
-
I have two tables that are getting conflicts between the subscriber and the publisher, however I am pretty sure we only update these tables at the subscriber.
I have column level tracking turned on.
The tables both have Nonoverlapping, single subscription (3) set for partition options. I wonder does this do any maintenance to the tables?
I have put in place some triggers to audit what makes changes to the data, but won't know until tomorrow, so if there is something that anyone knows about that might help, please let me know.
Thanks
-
Same happen to me, installing sp4 may help you.
see this from the MS website.
Preventing False Conflicts
Retention-based meta data clean up prevents non-convergence and silent overwrites of changes at other nodes. However, false conflicts can occur if the following conditions are met:
- The meta data is cleaned up at one node and not another.
- A subsequent update at the cleaned-up node occurs on a row whose meta data was deleted.
For example, if meta data is cleaned up at the Publisher but not at the Subscriber, and an update is made at the Publisher, a conflict occurs even though the data appears to be synchronized.
To prevent this conflict, make sure that meta data is cleaned up at related nodes at about the same time. If -MetadataRetentionCleanup is set to 1, both the Publisher and Subscriber are cleaned up automatically before the merge starts, thereby ensuring that the nodes are cleaned up at the same time. If a conflict occurs, use the merge replication conflict viewer to review the conflict and change the outcome if necessary.
If an article belongs to several publications or is in a republishing scenario, it is possible that the retention periods for a given row at the Publisher and Subscriber are different. To reduce the chance of cleaning up meta data on one side but not the other, it is recommended that those different publications have similar retention periods.
Note If the system tables contain large amounts of meta data that must be cleaned up, the merge process may take longer to run. Clean up the meta data on a regular basis to prevent this issue.
-
Interesting stuff thanks Pedro but may not be relevant as I am using SQL 2005 - so obviously no SP4 just yet.
Might be very useful for other projects we have done though and there may be some crossover between 2000 and 2005. We'll investigate further.
-
The triggers I put in place to audit the updates to the table I am conerned with revealed that there were no updates occuring that were not expected.
So, what happens is the data gets inserted via a biztalk process. The data then gets synchronised to a mobile device where an update occurs.
Upon synchronisation, the data updates going back are met with a conflct - wwhich must surely be all but impossible if nothing has touched the data on the server.
To make matters worse, this doesn't occur for every updated record only for some of them. It also does not occur to other tables set up in the same way.
Something is very fishy about this..
-
An explanation of how (not why) this is happening.
In my database there is a parent child relationship defined.
The child table is the one getting the conflicts.
The conflicts occur because of an update to the parent table.
Basically, it works like this.
A job has meters.
A user gets assigned a job.
Eventually, the user starts the job.
Once the user starts the job, they update the meter readings. (update to meter table out in the field)
Meanwhile, at head office, the job changes priority (escalates). (update to job table at the server)
Synchronisation happens and a conflict occurs because the job table has been updated!
Apparently, when the parent table gets updated, data relating to its child records (in this case meter) is inserted into the MSmerge_contents table. The meter table itself is not touched.
Because there is a change at the client for the meter record, SQL raises a conflict.
That only took nine months to discover.
Now if someone could only tell me why this happens
I am sure I could design my database around this behaviour, but if I don't update a table at both ends, I don't expect to see a conflict. It shouldn't even be an issue.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply