We are using SQL 2005 (SP1) and mobile agents with SQL Mobile. We are seeing an excessive number of updates after a device's database is reinitialized. My understanding was that if I generate a new snapshot or reinitialize all subscriptions (from the server), the devices would only get what the database looks like right now (inserts only) after syncing and/or reinitializing, but they are actually getting the right number of inserts and a huge number of updates in Replication Monitor. Any ideas?
Thanks!
Mike
Update: Found a weird thing with the updates: On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts. On production, the number of updates being transferred is 6 times the number of inserts on every article, so it the mobile database should get 10 rows for an article (based on filters), it will show 10 inserts and 60 updates.
Hope this helps, although it seems pretty strange!
Mike
|||are the any configuration difference or data load difference between the test server and the production server ?
and also, by saying "On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts", you meant to say the "updates on the subscriber is the same number as the inserts on the subscriber", right ?
what is you publication looks like ? e.g. number of articles, RIs between them ? etc.
that'll help us to identify the issue.
thanks
Yunwen
|||First of all, I have an update. We recreated the publication on our test server as well as our production server (causing outages to our customers). After doing so, everything went back to normal and we were only seeing inserts (no updates) after a subscription is reinitialized. Now, just this morning, our production server started showing duplicates again, right now the number of inserts is exactly the same as the number of updates. Another thing to note is that right before we recreated the publication in production, the ratio was up to 7 to 1 (updates to inserts), meaning that it went up again.
As to your question, we have 23 articles. All of them have filters based on HOST_NAME(), some have even more complex filters that use subqueries based on HOST_NAME as a simple join will not work. All tables have PK/FK relationships with the exception of several tables that have auditing fields (created by, modified by, etc). We do not have referential integrity as the assosiated data is not always transferred to the device, nor do we use these fields on the device except to set or update them. The only exception to this is a LOG table that contains a trigger to move the logs into a historical table so that we can gather information from the device. This produces an insert during the upload and a delete during the download, removing the log from the device during a sync.
BTW, you are correct, the number of updates to the subscriber matches (or is some multiple of) the number of inserts on the subscriber. So far, we do not see this with normal syncs, only reinitializations.
One other thing to note is that we recreated the publication yesterday at around 7AM CST. This morning is the first time we are seeing the updates again, and they started sometime between yesterday evening (when the last reinitialization was recorded; worked fine) and this morning at 9:30AM (the first reinit that showed the duplicates). That being said, it took over a month for the previous publication to reach a multiple of 7.
Hope this all makes sense! This is getting more and more urgent as we send nearly 20,000 rows to some devices, meaning that their reinit is starting to take a larger and larger amount of time as the multiples go up.
Thanks!
Mike
|||Thanks for the update.
do you generate a new snapshot prior reinitializing the subscriptions ? if the snapshot is old one and the subscription was reinitizlied. it will apply the snapshot and then apply the data in the mscontent table and if there are more and more updates/inserts on the publisher, this will give you this impression that subscriber was getting some unnecessary data.
also, you mentioned that there is a trigger on one of the published tables. is it possible you provide more details about it, or event better, send us the the publication script and the creation scripts for all the tables ?
Thanks
Yunwen
|||First, yet another update: We removed and recreated the distribution database and publication after doing so on our test server and seeing the problem go away (usually we see some sort of duplication before a week has passed). So far so good, although it has not yet been 24 hours on the production side.
At the time of recreation in production, we were up to 5 times the number of updates as inserts, which means that it was going up by a multiple of 1 almost every 24 hours (although some days it skipped and the time the updates increased moved around; hopefully this removes scheduled jobs as a possible cause). I copied the distmdl and distribution files out before we recreated the distribution DB so that we can review a few of the critical system stored_procedures for differences, just in case the service pack did not update existing distribution databases correctly.
As to your questions...
Yes, we have tried generating a new snapshot to see if that would fix the issue. It had no effect. I agree that we may see some updates in there, but to have an exact multiple on every table would still not seem right to me. I would expect to see "random" updates throughout the articles.
I will look at scripting out the rest of the publication, but here is the trigger:
CREATE TRIGGER [dbo].[MoveLog]
ON [dbo].[LOG]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION
INSERT INTO LOG_HISTORY SELECT * FROM [LOG]
DELETE FROM [LOG]
IF @.@.error <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END
SET NOCOUNT ON;
END
Thanks,
Mike
|||Almost forgot, we have a fax table that functions the same way as the log table. Also, both the log and fax table have no filters (as they are emptied after each sync;have yet to see them leave data in the table on the server or in any of our test SQL Mobile databases).
The triggers are identical to the point that you could just replace "LOG" with "FAX" and you would have it (in fact, that's how the fax trigger was created!).
Thanks again!
Mike
|||Thanks for th update for this issue. yes, seeing update is exact X * insert number does indicate there could be issues.
so is both Log and log_history was published ?
Please keep us updated on the issue.
thanks
Yunwen
|||Only LOG is published. Basically, the device logs to the log table. During a sync, the log entries are uploaded, the trigger fired (to move it to the history table), then the log entry is deleted off the device, during the download phase.
I am now working with MS support on the issue, so hopefully we can figure something out. So far, I am just gathering logs for review.
Thanks,
Mike
|||Thanks Mike.
Working with MS support probably a good way to pursure this issue. they can instruct you how to trouble shooting this issue.
Thanks
Yunwen
|||Hello,
we are running into the same issue (multiplied insert, updates, initial synchronization taking up to 10 times longer than on identical publication on sql2000). I am therefore very concerned if you finally solved the case?
Thanks in advance
Kuba
|||We are running into this same issue as well. It started out as updates matching the number of inserts, and has now gone to updates doubling the number of inserts. If anyone has any solutions or feedback on this issue, it'd be greatly appreciated!Thanks,
Adrien.
|||I have contacted Microsoft developer support and resolved this issue with them. It is a known issue and they have a hot fix for it. However, the fix won't be available publicly until the next service pack. If you want to resolve this issue sooner though, developer support will refund the cost of an incident since it is a known issue.
Adrien.
|||
Hello,
are you sure whether the hotfix to the discussed problem is going to be available in SP2? I've seen the list of bugs fixed by SP2CPT and I can't find anything that seems to be related to the case. How do I conctact developer support, is it possible to obtain the single hotfix from them or from anybody else?
Thanks in advance
Kuba
|||Hello,My contact at developer support told me that the hotfix would be available in the next service pack. Whether he meant for SP2, or a future service pack, who knows? If you can't wait for it to be ready in a service pack, yes you can get it from developer support. To contact them, browse to the MSDN home page while logged into MSDN, then click on "Support Incidents".
Adrien.
No comments:
Post a Comment