MSmerge_history - Production Issue

Thank you so much for responding Hillary.
I checked and for all 4 publications the value is set to "True" for
Allow anonymous subscriptions. We do not need to allow this.

Can this simply be changed to False while replication is running?

Ths Subscription Expiration for all 4 publicastions is already set to
never keep metadata longer than 14 days.

When you say I should also be able to delete the merge history beyond
the retention you are referring to the MSMerge_history table in the
distribution database correct? Just delete like you said in small
batches basing it off of the time column so records returned from this
query would be acceptable to delete:

select count(*) from msmerge_history
where msmerge_history.time >= getdate() - 15

Once I delete these records would you think it would be safe for me to
try and run the "Agent history clean up:
> distribution" job without filling up distributions transaction log?

Do you feel that the large number of records in the distribution
database for msmerge_history is what is causing the "Agent history clean
up: > distribution" job to fill up the TL? Aside from the anonymous
subscriptions setting is there something else I should have done
differently?

I really cannot thank you enough for your assistance.
I've been checking this posting all day but for some reason just saw
your response.

Thank you again, this is really a huge issue for me and your help is
appreciated.

Barbara








*** Sent via Developersdex http://www.developersdex.com ***
 >> Stay informed about: MSmerge_history - Production Issue 

Don't change allow anonymous to false while you have active subscriptions. If
you do you will be forced to reinitialize all subscriptions.

Yes, delete the rows from msmerge_history in small batches. Once done you
will need to keep the clean up agents running. I would run them through query
analyzer to see where they are failing and fix the problem.

Its hard to say where you tlog problem is coming from. It might be from the
cleanup failure or from another source.
"SQL Barb" wrote:

> Thank you so much for responding Hillary.
> I checked and for all 4 publications the value is set to "True" for
> Allow anonymous subscriptions. We do not need to allow this.
>
> Can this simply be changed to False while replication is running?
>
> Ths Subscription Expiration for all 4 publicastions is already set to
> never keep metadata longer than 14 days.
>
> When you say I should also be able to delete the merge history beyond
> the retention you are referring to the MSMerge_history table in the
> distribution database correct? Just delete like you said in small
> batches basing it off of the time column so records returned from this
> query would be acceptable to delete:
>
> select count(*) from msmerge_history
> where msmerge_history.time >= getdate() - 15
>
> Once I delete these records would you think it would be safe for me to
> try and run the "Agent history clean up:
> > distribution" job without filling up distributions transaction log?
>
> Do you feel that the large number of records in the distribution
> database for msmerge_history is what is causing the "Agent history clean
> up: > distribution" job to fill up the TL? Aside from the anonymous
> subscriptions setting is there something else I should have done
> differently?
>
> I really cannot thank you enough for your assistance.
> I've been checking this posting all day but for some reason just saw
> your response.
>
> Thank you again, this is really a huge issue for me and your help is
> appreciated.
>
> Barbara
>
>
>
>
>
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
>
 >> Stay informed about: MSmerge_history - Production Issue 

Thank you Hillary. I responded on Windows Live as well. Can you please
tell me which distribution cleanup task are you referring to:

Agent history clean up: distribution

Distribution clean up: distribution


The Distribution clean up: distribution job has always been disabled by
default when I created the publications and the Agent history clean up:
distribution job has been enabled. Should I be running both or just
one?

And thanks for the heads up about changing the publication to allow
anonymous to false while I have active subscriptions.

Thank you again

*** Sent via Developersdex http://www.developersdex.com ***
 >> Stay informed about: MSmerge_history - Production Issue 

Both of them should be running.

Note that you should set the history retention to the same value as the
publication retention. Doing this will not force reinitialization.


"SQL Barb" wrote:

> Thank you Hillary. I responded on Windows Live as well. Can you please
> tell me which distribution cleanup task are you referring to:
>
> Agent history clean up: distribution
>
> Distribution clean up: distribution
>
>
> The Distribution clean up: distribution job has always been disabled by
> default when I created the publications and the Agent history clean up:
> distribution job has been enabled. Should I be running both or just
> one?
>
> And thanks for the heads up about changing the publication to allow
> anonymous to false while I have active subscriptions.
>
> Thank you again
>
> *** Sent via Developersdex http://www.developersdex.com ***
>
 >> Stay informed about: MSmerge_history - Production Issue 

Hello,

I’m sorry to have to post again but I just want to clarify some things
so that I can understand and take care of thess once and for all. I
realize that this post is very lengtly, because believe it or not I have
spent a lot of time since my original post looking into merge
replication metadata cleanup but I’m starting to think I’ve only become
more confused by it. If you would be so kind as to assist me again I
would greatly appreciate it.

Specific to my Issue:
It is not expected that the subscriber would ever be offline, however to
plan for an emergency I think 4 days would be sufficient. So I’d need
to change both the Subscription Retention and History retention to 4
days, following your directions from an earlier post.

In our development environment I changed Transaction retention / Store
Transactions, History Retention, and Subscription retention all to 4
days. But when I run the Agent history clean up: distribution and the
Distribution clean up: distribution jobs records in MSmerge_history
table are not being deleted, when there are clearly records with a time
value older than 4 days. When I run (SELECT * from msmerge_history where
msmerge_history.time <= getdate()-4) the results are around 65K records.
I’ve tried stopping and then restarting the Merge jobs and I’ve also
tried stopping and restarting the SQL Server Agent and still no records
are deleted. What am I doing wrong? The only thing I can think of is
that all publications allow anonymous subscriptions; could this setting
be what is keeping the old data from being purged?

In General:
Here are some more generalized questions I still have about the merge
replication cleanup process…
Are Subscription Expiration and Publication Retention are the same
things?
Do the Transaction retention / Store Transactions: values only matter
if you are using Transactional Replication?

My understanding is that metadata cleanup in merge replication is also
performed every time the Merge Agent runs for a subscription by the
stored procedure sp_mergemetadataretentioncleanup and that it removes
metadata older than the publication retention period from the following
system tables on both the publisher and subscriber databases:

• MSmerge_contents
• MSmerge_tombstone
• MSmerge_genhistory
• MSmerge_current_partition_mappings
• MSmerge_past_partition_mappings
• MSmerge_generation_partition_mappings

My questions regarding this are:

If this is done every time the Merge Agent runs for a subscription what
happens if our Merge Agent is set to run continuously?
Do I need to create a separate job to execute this stored procedure?
(DELETE * from msmerge_history where msmerge_history.time <=
getdate()-4)
If so, on what type of schedule should it be run (every 10 minutes)?

Currently there are no jobs scheduled on Subscriber for any type of
cleanup. I am assuming that they are handled by jobs that run on the
publisher side?

I noticed that on all 4 of the publisher databases the table
MSmerge_history is empty but on the subscriber databases this table had
at this time 12K+ rows? Is this normal?

I’ll gladly try putting all of this information together in a more
comprehensive format and posting it back in hopes that it will be
beneficial to others. It looks like there has been a lot of chatter
about this lately.

Again thank you very much for your time and insight. Barbara


*** Sent via Developersdex http://www.developersdex.com ***
 >> Stay informed about: MSmerge_history - Production Issue 

Barbara - you might have seen it but if not have a look at this article which
explains things :
http://msdn.microsoft.com/en-us/library/ms151778(SQL.90).aspx
In particular "It is possible to specify that subscriptions never expire (a
value of 0 for @retention), but it is strongly recommended that you do not
use this value, because metadata cannot be cleaned up."
The merge agent may run continuously but this is a little misleading, as it
uses a polling interval to initiate, and this means that the cleanup still
gets applied every 60 secs so there's no need to schedule a job to do it.
HTH,
Paul Ibison
 >> Stay informed about: MSmerge_history - Production Issue 

Paul,

I apologize for my delay in saying thank you very much for taking the
time out to respond to my post.

You set me straight about the continuously running merge agent; you are
correct I was deceived.

I have looked at the article you mentioned above, re-read it again just
to make sure I was not missing anything. I believe I understand the
@retention setting; we definitely do not want to metadata not to be
cleaned up but even after I changed this setting to 4 days in our
development environment records are still not being deleted. So my
biggest concern right now is why are records still not being deleted
from MSmerge_history in the distribution database even though I’ve
changed Subscription Retention and History retention to 4 days?
This appears to go for records in the publishing databases as well.

I just checked MSmerge_genhistory and there are records in there from
04/2007. msmerge_tombstone and msmserge_contents do not have a date
column I can search on but those tables appear to just be growing as
well.

I don’t like creating “special jobs” to handle this when the system
generated replication jobs should be taking care of it but at this point
I don’t know what else to do. I do not want to run into the same issues
that prompted me to create this post originally. I’m open to any
suggestions but if I do create my own job is this sufficient:

DELETE from msmerge_history
WHERE msmerge_history.time <= getdate()-4

Could you (or anyone, I’m all ears) answer the following questions?

• No replication clean up jobs are scheduled on Subscriber; is this
handled by jobs that run on the publisher side?

• MSmerge_history is empty in the publishing databases but on the
subscriber databases this table has 12K+ rows? Is this normal?

• Are Subscription Expiration and Publication Retention the same
things?

• Do the Transaction retention / Store Transactions: values only
matter if you are using Transactional Replication?

I appreciate everyone’s patience in this matter, I’m just really stumped
at this point at to why cleanups don’t appear to be happening.

Thank you again for your time and help,

Barbara


*** Sent via Developersdex http://www.developersdex.com ***
 >> Stay informed about: MSmerge_history - Production Issue 

Hi Barbara,
there's definitely something else going on in your system that we are not
aware of. Perhaps there is another publication involved? You could
double-check to try to understand the source of the rows by looking for
values of the rowguid column which are not in the published tables.
Alternatively if this is not too painful, you could reinitialize. As another
alternative, there is a similar thread with some scripts which was posted in
the last couple of days which you could use.

(btw the retention period for the publication is how long the metadata is
kept, while the subscription expiration period is how long subscribers are
allowed to exist without syncing up - logically you'd expect them to be set
the same, but this is not mandatory).

HTH,

Paul Ibison
 >> Stay informed about: MSmerge_history - Production Issue 

Paul,

Thank you very much for taking the time to respond to my post. I
appreciate your help. I will take a look a the other post that you
mentioned.

Thank you again,

Barbara


*** Sent via Developersdex http://www.developersdex.com ***
 >> Stay informed about: MSmerge_history - Production Issue