November 23, 2005 at 4:33 pm
Hello everyone,
I have a very perplexing problem that has persisted long enough to make it the highest priority for me right now. Please give a moment to consider what might cause a problem like this:
Our database loses about 70 to 100 rows FROM THE SAME TABLE whenever we initialize a new merge subscriber. We are using pull subscriptions. I do the initialization as soon as possible after creating a snapshot and there are few if any conflicts after doing so. Yet, the last few times we have had about 70 to 100 rows missing from the same table every time. They are not the same exact rows each time, but they are similar in that they are recent (meaning they were inserted into the table within the last few months. The table holds data going back about 3 years). So far I’ve used Red Gate’s SQL Data Compare tool to compare the database before and after initializing a new merge subscriber which allows me to reinsert the missing rows back into the database fine. (Thankfully!) Again, it’s only 1 table that has this problem (out of about 90 or so) and it only happens when initializing a merge subscriber.
Recently, I ran profiler while initializing a merge subscriber and captured everything in a trace, but I don't know how to interpret the plethora of data that it captured. I loaded the trace data into a table and queried the TextData column for the name of the table in question, but I didn't see anything obvious in those results that raised a red flag. I'm unsure what to look for or what to try.
The most recent thing that I am trying is to turn the @compensate_for_errors parameter (introduced with SQL 2000 Service Pack 4) back to TRUE, since I seem to recall that we didn't really have any problems with the table until sometime after I turned that parameter to FALSE (as recommended by Microsoft and various newsgroups). I will report my findings if it turns out that changing that parameter helped.
Has anyone else seen anything like this? I've seen other newsgroup postings where people have complained of missing rows with merge replication, but most of them were too vague to be able to reach any kind of conclusion with, or they had no responses to their question at all. Could I have a corrupted index or something? I feel I’ve tried everything practical that I can think of (short of dropping all the subscriptions, dropping the publication and recreating everything). Other than this problem, replication is working fine.
FYI
I am using SQL’s automatic identity range management on the table in question. I haven’t had any identity collisions and the identity seed on the publisher (which happens to be where all of the inserts and updates on this table occur with this table) is in the range of the identity check constraint automatically created by SQL Server.
Also, there is a second merge publication which also includes this table. I have had no problems with that publication or it’s subscriptions so far. All the merge article parameters (including identity ranges, @compensate_for_errors, etc) are identical for all articles on both publications, with the exception of subset filters. The second publication’s subset filter’s have a greater date range; it publishes more historical data than the first publication.
I’m sure I might have overlooked something – or perhaps there is something I need to check again. Please help if you able!
Thanks sincerely,
Nate Cook
Programmer/Analyst (and accidental DBA)
November 25, 2005 at 11:25 am
Update:
Unfortunately, changing the @compensate_for_errors parameter value back to True did NOT help.
Please assist me with your brainstorming!
Nate
November 27, 2005 at 3:46 pm
Just checking if you have installed the necessary hotfix at all subscribers to take advantage of the compensate_for_changes proptery?
The article at http://support.microsoft.com/kb/828637 talks about this in the remarks.
It might be something you have overlooked.
November 28, 2005 at 6:58 am
Steve,
Thank you for your reply! If I understand the knowledge base article correctly, no hotfix is required if you have Service Pack 4 installed (which I do). Did I misunderstand?
P.S. The most recent thing I tried is running the following commands:
DBCC CHECKDB('DatabaseName')
DBCC DBREINDEX ('OffendingTableName')
DBCC CHECKCATALOG('DatabaseName')
I have not yet been able to confirm if running these commands fixed the problem or not; I tried it because it occured to me that possibly there is actually some corruption in the database?! I didn't receive any error messages when running these commands, so I'm unsure if they fixed any errors or not. Time will tell...
Any other ideas folks?
Thanks!
Nate
November 28, 2005 at 1:59 pm
I'm not sure of you're topology, but so long as you have SP4 at all subscribers as well as the publisher then the data shouldn't be going missing.
The other thing to possibly consider is that the filtering of the records is not as accurate as you might like.
What I have found over the last month is that when you have a complicated filtering set up, you are more inclined to encounter these types of errors. It may well be worth your while to investigate the filters and confirm their validity.
November 28, 2005 at 6:10 pm
Umm maybe a silly question but have you looked at the conflict tables??? do they shed any light on this?
------------------------------
Life is far too important to be taken seriously
November 30, 2005 at 9:10 am
Steve,
Thank you for your reply. I checked my join filters again to make sure that they are accurate. In this regard, one thing I should point out is that the enigma of the missing rows did not occur at the outset. I initialized 3 or 4 subscribers within a week and didn't lose any rows that I'm aware of. I haven't changed the configuration of the join filters since initially setting up the publication. Thinking about that fact leads me to believe that it is more likely to be some type of corruption that has taken place to cause the problem rather than a configuration issue. But thank you for that suggestion!
By the way, all subscribers are running MSDE w/Service Pack 4.
Adam,
Thank you also for your reply! Actually, your question is not a silly one at all. Of course I viewed conflicts with the conflict viewer but I hadn't actually examined the conflict tables themselves. Do you have any suggestions of what to look for as a red flag with the conflict tables? I know they exist for that particular table, if that's what you mean. Also, I know that they are empty (except when they actually have conflicts, of course). Is there something in the design view that I should look for?
I am going to DBCC DBREINDEX the conflict tables for the offending table now for the heck of it. Can't hurt anything.
Thanks everyone for your help,
Nate
December 7, 2005 at 2:53 pm
ALL,
I finally figured it out. There was an update trigger on a parent table that was causing the missing rows! This was the only trigger in the whole database (aside from replication triggers)! I didn't think there were any triggers in the database, so I never thought to check! What's strange is that the trigger worked fine before replication. Why would replication cause the trigger on the parent table to delete more rows than it should? I know the answer has something to do with the fact that the tables have join filters on them - but I wish I understood that better.
Thanks for everyone's help. I hope this information helps someone else avoid the nightmare this has been.
Nate
December 7, 2005 at 4:56 pm
From my experiences so far, I have seen that the trigger can fail in the subscription database. This failure may be related to an actio nthat th trigger is taking - like an insert that fails because the referential integrity is broken due to the filtered view of the database at the subscriber.
SQL sees this as a failure to update accordingly and then the rest is a mystery. I would have thought the @compensate_for_changes = false setting would prevent the actual deletions.
You did mention that this was set to true though?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply