Large number of records in MSMerge_GenHistory

[Steve]

My MsMerge_GenHistory has too many records (currently 1.6 million rows) and I
can't get it to go down.

One of my Merge publications accidentally had Subscription expire in XX days
set to 70 days. My MsMerge_Contents and GenHistory filled up to 1.6 Million
and 1.9 Million records before I caught the error. I have corrected the
Parameter and have been able to get my msmerge_contents down to 28 K rows. I
can't get MsMerge_GenHistory to come down.

I don't want to set every publication that I have to reinitialize. The
publication that had the expiration days parameter problem is a remote
subscriber with a laptop in the field and I don't have access to it on a
daily basis. It is causing me problems with other publications with
subscribers getting unable to process GenHistory messages. I have all of my
remote subscribers set with a QueryTimeout of 4000.

What can I do?


Thank You

Steve

[Paul Ibison]

Steve,
have a look in BOL at sp_mergecleanupmetadata - you could run this to do a
manual cleanup of the metedata.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

[Steve]

What will happen if I run this with the Reinitialize Subscribers set to
False? I am trying to not reinitialize all of my subscribers.

I have found that most of my genhistory records, the pubid field is NULL.
Are these the problems and can I simply delete these?

I have a second question about a recommended max number of publications. I
have a database with 40 different publications . Each publication has 138
articles. Between all of these articles, there are in the neighborhgood of 1
million total records published. Some publications have multiple subscribers,
most publications have a single subscriber with all columns published and
static row filters. Most of these subscribers are remote users traveling. Is
this too publications? This topology has saved us from a system wide
reinitialize a couple of times when a particular subscriber has trouble or
simply will not sync in a reasonable timeframe (we have most of our
publications set to expire in 20 days).


Thanks for the help!


Steve

Post by Paul Ibison
Steve,
have a look in BOL at sp_mergecleanupmetadata - you could run this to do a
manual cleanup of the metedata.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

[Paul Ibison]

Steve,
running this and following the guidelines (stop all updates, sync all
subscribers etc) for @reinitialize_subscriber = FALSE should be fine.
As for the number of subscribers issue, I'd only say this is too many if you
have performance issues - if there is significant blocking or the resource
usage is too high when the merge agents run etc.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

[Steve]

One additional thing.

I noticed that most all of the records in msmerge_genhistory have a pubid of
NULL. A few have a with what looks like a valid guid. Are these records the
problem and can I simply delete them?

Thanks

Post by Steve
My MsMerge_GenHistory has too many records (currently 1.6 million rows) and I
can't get it to go down.
One of my Merge publications accidentally had Subscription expire in XX days
set to 70 days. My MsMerge_Contents and GenHistory filled up to 1.6 Million
and 1.9 Million records before I caught the error. I have corrected the
Parameter and have been able to get my msmerge_contents down to 28 K rows. I
can't get MsMerge_GenHistory to come down.
I don't want to set every publication that I have to reinitialize. The
publication that had the expiration days parameter problem is a remote
subscriber with a laptop in the field and I don't have access to it on a
daily basis. It is causing me problems with other publications with
subscribers getting unable to process GenHistory messages. I have all of my
remote subscribers set with a QueryTimeout of 4000.
What can I do?
Thank You
Steve

[Paul Ibison]

Steve - I wouldn't delete any of this manually - only use the standard
procedure to remove this metadata.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com