merge replications causes insert timeout

Hi All,

i need help. i have a merge replication running every 10 minutes. the replication is for 1 database consisting around 50 tables and all 50 tables are marked as article to be published. let's say the publisher is server A and the subscriber is server B. for explanation purpose, let's pick 1 table from the database, let's say table X

in server A, there are applications running that insert and update table X. in server B, there are applications running that select and delete table X. table X at average, has tens of thousands rows.

now comes the problem, every 10 minutes whenever the sql merge agent is running, sometimes whenan application insert a row to table X in server A, it returns an error : "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.". when i stop the replication (delete the subscriber), the row insertion to table X in server A seems fine. then i come to a doubtful conclusion, that the timeout error happened because when the insert statement executes, the replication locks the table for merging.

could somebody verify this? if my conclusion is true, then how am i suppose to do this? (see bold text above). really2 appreciate the help

Yes, merge agent can fail due to deadlocking. WHat you need to ensure is that your application is taking the minimum locks necessary. This is no different than two applications accessing/modifying the same data simultaneously, consider merge replication a second application.

Review your queries, insert/update/delete statement and see how transactions are being used, what locking behavior, queries are written optimally, etc.

Merge replication sync fails with error code 80040E19 on handheld

Hi;

We are performing merge replication between SQL Server 2005 and SQL Server Mobile with more than 1100 handheld, and recently replications are terminating with eror code 80040E19.
We can only avoid the problem by deleting the related user's partition directory and reruning the related user's dynamic snapshot.

Any advice on this issue would be appreciated

Thanks,
Hakan Gümüs

I feel your pain. We only have 300 handhelds in the field but receive this error after every couple of weeks or so. We have not found much information on it at all. It seems to be a client side error, ie no error shows up in the server logs. What we do to alleviate the error is to right click on the publication in SQL Server and Reinitialize All Subscriptions, selecting the Use a new snapshot option with Generate now checked. Once the snapshot is done (monitor with View Snapshot Agent Status) the device replicates again just fine.

Due to the cyclic affect of when it occurs, it seems it may correlate to Retention period of the publication. This is difficult to say as we don't fully understand what happens when the expiration period is reached. The only thing we do know is that Reinitializing the subscriptions works every time.

It is also a guess that it may have something to do with the number of simultaneous replications occuring. Possibly some data at some point gets corrupted or out of sync. We did not see the error during our pilot when we only had a couple of dozen units in the field. Our users by the way replicate twice a day - morning and night, so there are periods of high activity.

Please, if you find out any additional information to this issue, let me know. It has plagued us now for over 6 months. I'll be happy to share whatever information that I can to help solve this issue for good.

Regards,
Santino Lamberti

|||

Hi Santino;

We have opened a case about this issue, I'll inform you about the result.

Thanks for your reply.

Hakan Gümü?

|||The Investigation about the case is still going on. The only workaround that we could find is dropping the related user's merge partition with below command and resync it.

sp_dropmergepartition 'TESTPUB',NULL,'THOSTNAME'

|||Thanks for sharing this. I'll try it next time we run across the error. It seems that I've tried doing this via the UI once before with no effect, but I'll give this a whirl anyway. I also plan on looking at the snapshot data (schema and bcp files) for the failing user and the master snapshot. The error seems to occur after all the schemas are applied and possibly when the data is being copied in.

Merge replication sync fails with error code 80040E19 on handheld

Hi;

We are performing merge replication between SQL Server 2005 and SQL Server Mobile with more than 1100 handheld, and recently replications are terminating with eror code 80040E19.
We can only avoid the problem by deleting the related user's partition directory and reruning the related user's dynamic snapshot.

Any advice on this issue would be appreciated

Thanks,
Hakan Gümüs

I feel your pain. We only have 300 handhelds in the field but receive this error after every couple of weeks or so. We have not found much information on it at all. It seems to be a client side error, ie no error shows up in the server logs. What we do to alleviate the error is to right click on the publication in SQL Server and Reinitialize All Subscriptions, selecting the Use a new snapshot option with Generate now checked. Once the snapshot is done (monitor with View Snapshot Agent Status) the device replicates again just fine.

Due to the cyclic affect of when it occurs, it seems it may correlate to Retention period of the publication. This is difficult to say as we don't fully understand what happens when the expiration period is reached. The only thing we do know is that Reinitializing the subscriptions works every time.

It is also a guess that it may have something to do with the number of simultaneous replications occuring. Possibly some data at some point gets corrupted or out of sync. We did not see the error during our pilot when we only had a couple of dozen units in the field. Our users by the way replicate twice a day - morning and night, so there are periods of high activity.

Please, if you find out any additional information to this issue, let me know. It has plagued us now for over 6 months. I'll be happy to share whatever information that I can to help solve this issue for good.

Regards,
Santino Lamberti|||

Hi Santino;

We have opened a case about this issue, I'll inform you about the result.

Thanks for your reply.

Hakan Gümü?

|||The Investigation about the case is still going on. The only workaround that we could find is dropping the related user's merge partition with below command and resync it.

sp_dropmergepartition 'TESTPUB',NULL,'THOSTNAME'

|||Thanks for sharing this. I'll try it next time we run across the error. It seems that I've tried doing this via the UI once before with no effect, but I'll give this a whirl anyway. I also plan on looking at the snapshot data (schema and bcp files) for the failing user and the master snapshot. The error seems to occur after all the schemas are applied and possibly when the data is being copied in.

merge replication corruption (system triggers and views)

All of a sudden none of our merge replications are working. In fact you can't even insert, update or delete and data from the tables in the merge publication. When trying that, we get an error stating:

Msg 550, Level 16, State 1, Procedure MSmerge_ins_E3F43EF8B259476099BBB194A2E1708C, Line 42
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.

Currently, the only solution I've found is to delete the publication and recreate it. I'm trying to figure out why this happened. It happened on a development server that to my knowledge, hasn't been changed in a week or so outside of changing the server's IP address. Would that cause such an error to occur?

-mikeI found another change.. we added a linked server using sp_addlinkedserver.. Any thoughts?|||Does your merge subset filter clauses or join filter clauses contain views that contain WITH CHECK OPTION, pointing to remote table?|||No filters are set for the publication.|||

ok, then you have to trace your steps to see exactly what changes were made that would cause this, and see if you can back them out one by one.

any idea what the linked server would have to do with regards to the views, triggers, or any of the published tables? are you making changes from a remote machine?