We recently re-initialized a subscriber for merge replication, the snapshot
took 36 hours and finished successfully. The problem now is the merge agent
is giving the following error while trying to upload to the publisher:

The merge process could not retrieve generation information at the
'Subscriber'.

The source of the problem is the sp_MSenumgenerations stored proc taking
forever to run. I have tried running update statistics and dbcc dbreindex on
the msmerge_contents and msmerge_genhistory (which are very large) but the
stored proc is still taking forever.

The problem started immediatly after the new snapshot was applied.

Any ideas ?


--
Chris

Chris,
I'd have a look at increasing the query timeout value, or possibly doing a
nosync initialization. BTW what error message are you receiving?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Thanks for replying Paul.

The entire error message is:

The merge process could not retrieve generation information at the
'Subscriber'.
(Source: Merge Replication Provider (Agent); Error number: -2147201004)
---------------------------------------------------------------------------------------------------------------
The merge process timed out while executing a query. Reconfigure the
QueryTimeout parameter and retry the operation.
(Source: VANSQL (Data source); Error number: 0)

The query timeout is currently 1000 seconds (16 minutes). I did not want to
increase this as it seems too large anyways and I was concerned something
else is wrong. What makes this different then snapshots in the past is it is
a re-initialization of an existing subscriber instead of a brand new
subscriber.

I have never understood why the snapshot agent sends out the entire contents
of msmerge_contents and msmerge_genhistory to a new subscriber, I do not see
what use it could be to a new subscriber.

In the case of a no-sync initialization do these files and their contents
also get sent ?

Thanks again.
--
Chris


"Paul Ibison" wrote:

The other thing I noticed was the exact call that is causing the problem is:

exec sp_MSenumgenerations 0, '9D3CKJ3F-73FD-423E-9335-7FBJH6BF096D', 1


I could be wrong but isn't the first parameter the genhistory reference so 0
would include all rows ?

Chris.

Chris,
for a snapshot that took 36 hours, I'd increase the querytimeout to a huge
value. In fact I'd almost certainly do a nosync initialization, and use
Winzip 9.0 to compress the snapshot folder before transferring over. This'll
save loads of time.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Chris,

spot on. From the proc:

select DISTINCT generation, guidsrc, art_nick, guidlocal, pubid, nicknames,
0
from dbo.MSmerge_genhistory
where generation >= @genstart

Cheers,

Paul Ibison

Thanks again Paul.

One last question. I tried a test of a no-sync initialization on a test db
and it still appeared to bcp out the contents of msmerge_contents and
msmerge_genhistory. In my case the combined is many GB's so it will take
hours.

Does it really bcp the entire contents or just current rows ?

Chris.

You're exactly right - it bcps the entire contents. However there is the
possibility of running sp_mergecleanupmetadata beforehand (ie before the
nosync copy), in which case the metadata won't exist. This has to be done
carefully (please see reference in BOL to above stored proc). The message
returned from the merge history in this case still refers to bcping x rows
into ms_merge_contents where x is the rowcount of the actual user table,
even though there are 0 metadata rows in the source and destination after
synchronization, so this is clearly a generic message.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

I think it might be time to drop and recreate the publication with the 14 day
subscriber expiry.

Do you have any idea why in my current situation the sp_MSenumgenerations is
being called with a genhistory value of 0 when the snapshot sent out was
current ?

Chris.

My guess is that it is for potential conflict resolution with other
subscribers who haven't yet synchronised.
Cheers,
Paul