Merge FAQs

Why does tempdb sometimes run out of space when creating a snapshot which uses dynamic filters?

Did you specify @use_partition_groups=true for your publication? If it is set to false, SQL merge replication may heavily use tempdb and a huge transaction log may be generated. See this article for more info.

My merge (2005) publication is hanging on initialization - what should I do?

There's loads of mentions of this recently. It's caused by an infinite loop problem and is bypassed by using :

Update sysmergepublications set generation_leveling_threshold = 0

This problem be avoided by applying a hotfix, or by applying cumulative update #8, or sp3. Full details are here.

Is it possible to write conflict info to a separate (persistant) table?

MSmerge_articlehistory has a count of conflicts per article, while MSmerge_conflicts_info has more detailed info including the conflict type and the 2 nodes which are in conflict. Polling these tables and writing the values to your own history tables is the main option for reporting.

Is there a way in SQL Server 2005 to set "not for replication" flag on an existing identity column?

Have a look at the new procedure sp_identitycolumnforreplication

How can I get round the (conflict) problems encountered in SQL Server 2005 when using GetDate() in filters (Nate Cook)?

Nate found that he was getting bogus conflicts intermittently. The problem did not occur in SQL Server 2000 and was specific to using getdate() in the subset_filter for a table - especially if you have other child tables being filtered by join filters to the main table which has that subset_filter. He worked with Microsoft for a month sending them metadata log files, etc. and now there is an MSDN entry describing the workaround. The only thing not described in the link is the fact that a potential symptom of using getdate() in filters is the existence of intermittent bogus conflicts: e.g. "The record was updated at the subscriber and at the publisher. Which version do you want to keep?" when in actuality the record was only updated at the subscriber and not at the publisher. Or it might say the record was deleted at the publisher when it wasn't at all.

How do I control the flow of data using merge replication in SQL Server 2005?

Have a look at the @subscriber_upload_options in sp_addmergearticle. There are older alternative options which are now no longer recommended:
(1) SQL Server Upload Only Conflict Resolver. BOL: "Changes uploaded to the Publisher are accepted; changes are not downloaded to the Subscriber". (set at design time for the publication)
(2) the -EXCHANGETYPE parameter (can be set at run time for the subscription)

What are the issues when combining filters and joins?

There are many issues which you need to be aware of when combining filters and joins and this could be made into a huge article. As an example for this section: if you have join filters, and a different static table filter on each table in the join, the results are not filtered correctly, or at least not at all intuitively. E.G. consider I merge replicate the 2 tables: Customers and Orders from the Northwind database. These are joined by a PK - FK relationship so a customer can place several orders. If I create a join filter to link the tables, all 'ordering' Customers and their associated Orders are replicated. I then add a filter clause on the Customers table (parent) to restrict the output to a particular customer: customerid = 'VINET'. The resulting (automatically created) merge view for Orders restricts the results to 5 orders - those belonging to customer 'VINET'. So, on initialization one customer and 5 associated orders are replicated. However, what if I also put a different filter on the Orders table: customerid = 'ALFKI'? Logically this should further restrict the replicated Orders. However, on initialization 11 orders are now replicated! In this case a view is created which unions the 2 clauses, rather than an AND clause being used. This means that rows arriving as part of the join, but which don't have a customerid = 'ALFKI' are additionally returned! This also occurs in SQL Server 2005. See Lizet's blog for other issues.

How can I dynamically change a filter clause?

Using static filters, it is possible to reallocate records at the highest level, and the effect will propagate downwards by virtue of the join filters. This will allow you to simulate changing the filtering dynamically. Practically speaking, there needs to be a separate table which relates filter details to subscribers, and table joins would then relate this assignment table to the other articles.

How can I find the list of pending merge changes?

Have a look at this script.

How can I manually start the merge agent outside of SQL Server agent?

The simplest way is to use the replmerg executable:
C:\Program Files\Microsoft SQL Server\80\COM>replmerg -Publisher DH1791628 -PublisherDB xxxPublisher -Publication xxxPublishertClients -Subscriber DH1791628 -SubscriberDB sub1 -Distributor DH1791628 -DistributorLogin sa -DistributorPassword sa (no not really in practice!!!)

Why are inserts on the publisher being removed?

This is very likely related to the "compensate_for_errors" setting. If a change from publisher fails to get applied at the subscriber (for some reason, PK, FK, CHECK,etc constraints), then synchronization will undo the change at the publisher. So an insert from publisher which fails at the subscriber will later on get deleted at the publisher. Similarly a delete from publisher which fails at the subscriber will get re-inserted at the publisher. The inverse also applies if changes initiated at the subscriber can’t be applied at the publisher. This behaviour is set at the article level and can be disabled if required ( there is an associated KB article for more info).

To determine for sure if this is the cause of the issue you are seeing is difficult. There is no extra column in MSmerge_articlehistory to show that the change being implemented is related to this setting and I have requested such an extra column to be added. You might be able to infer this is a compensating change from looking at MSmerge_sessions if there is a download conflict and then an upload delete in 2 adjacent sessions – but obviously if you have 100s of changes being applied at the same time this’ll be impossible as it will get lost in the avalanche of recorded changes.

To see if this setting is enabled, take a look at this type of query:

select name, compensate_for_errors from dbo.sysmergearticles

This query will tell you if the setting has been altered recently:

select * from dbo.MSmerge_settingshistory

How can I troubleshoot network (connectivity) issues?

Run a ping -t for a day and then look at the statistics to see if a hiccup occurred.

How can I report on the historical amount of updates, inserts and deletes done by publisher and subscriber over a period of time?

The key table is called MSmerge_sessions. This has data recorded much like the data in the replication monitor for merge replication - the number of inserts, updates, deletes, conflicts for uploads and downloads. It also has the number of metadata cleanup records deleted. MSmerge_articlehistory has the same data, but on a per-article basis.

How can I fix rows not being transferred on synchronisation?

This can happen in 2 circumstances:
(1) If you bulk insert the rows and accept the defaults, then "FIRE_TRIGGERS" is false and consequently the rows are not added to MSmerge_contents.
(2) If you do a fast-load using the Transform Data task in DTS/SSIS.
In all cases you need to run sp_addtabletocontents to include the rows and then resynchronise. Alternatively you can use sp_mergedummyupdate for a single row. For the fast load case, in future if you deselect the check box in the SSIS/DTS data flow, the merge insert trigger will fire.

How can I avoid subscriber deletes i.e. when I use filtered articles and add a record on a subscriber it is later deleted?

Filtering works by creating a view on the publisher. Initialisation doesn't send this view to the subscriber, and it is only checked on the publisher. So, you can add a record on the subscriber and on synchronisation this record will be sent as an insert to the publisher. Here, the filter is checked, and a delete is downloaded to the subscriber. If this is not the way you want it to work you have 2 choices: (a) modify the insert trigger on the subscriber to ensure it doesn't write the changed record to MSmerge_contents. (b) use partitioned views to partition your data based on the original merge replication filter and only replicate one table.

When the merge agent runs I notice there are blocking issues and then there is a timeout.

To find out more information about the cause of your blocking issues you might like to use profiler and the "blocked process threshold" report. If it is simply a matter of having a lot of data modifications being synchronized then you need to optimize the merge agent. You could:
(a) increase -DownloadGenerationsPerBatch
(b) make sure the metadata is being cleaned up
(c) run the merge agent more frequently, so as to not accumulate changes
(d) decrease the -PollingInterval if you are running continuously
(e) increase the -QueryTimeOut
Alternatively, if you accept a high latency, you might run the merge agent out of hours.

Error Messages: "The process could not enumerate changes at the 'Subscriber'."

This is a generic , high-level error message simply indicating that the merge agent ran into a problem moving changes from the subscriber to the publisher. It can be caused by several different problems, and is not necessarily a bug. To get more info, you could enable logging on the merge agent (-output c:\somefile.log -outputverboselevel 3). Alternatively, most of these merge errors are solved by simply restarting the merge agent.

Error Messages: "Failed to enumerate changes in the filtered articles"

There is now a KB article related to this issue for SQL Server 2000.

When I insert a row on Site1 (the Publisher) the row gets replicated to Site2. Site2 is a republisher to Site3 but the row never arrives at Site3.

Check to see if you are using local subscriptions. All subscriptions need to be global for this republishing setup to work.