From: PrafullSurana on
Hi,

I have 2 merge publication (pub7 and pub14)which have 7 and 14 days of
retention period. And i have 3 subscribers subscribing to above publications.

I wanted to know
1. when the meta data cleanup will happen for above publications?
2. When the metadata will be cleanedup for the subscriber?
3. Can i run the metadata cleanup sp say on day 10? Will this clean meta for
pub7?

Appreciate your help and guidance on above.

--
Thanks and Regards,
Prafull
From: "Charles Wang [MSFT]" on
Hi Prafull,
This issue seems same as your other post "Retention Interval -- merge
replication". I believe that you can find the answer from this article:
How Merge Replication Manages Subscription Expiration and Metadata Cleanup
http://msdn.microsoft.com/en-us/library/ms151778.aspx

Thank you!

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg(a)microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

From: PrafullSurana on
It looks the same issue but not.

What I learnt is, metadata cleanup is for database and not for publication.
Is this true?

In this post if you observe I have mentioned 2 publication with different
retention period and want to know behavior based on that.

As i can not efford to apply snapshot for every subscriber after every
14days even though they are in sync with server. I need to plan very well
about the retention period and even manual metadata cleanup to get optimum
performace. And to acheive that i would like to understand very well all the
concepts.

I had already gone through the link you mentioned, even before posting my
questions. My doubts were not clear.

Appreciate your help.

--
Thanks and Regards,
Prafull


""Charles Wang [MSFT]"" wrote:

> Hi Prafull,
> This issue seems same as your other post "Retention Interval -- merge
> replication". I believe that you can find the answer from this article:
> How Merge Replication Manages Subscription Expiration and Metadata Cleanup
> http://msdn.microsoft.com/en-us/library/ms151778.aspx
>
> Thank you!
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ===========================================================
> Delighting our customers is our #1 priority. We welcome your
> comments and suggestions about how we can improve the
> support we provide to you. Please feel free to let my manager
> know what you think of the level of service provided. You can
> send feedback directly to my manager at: msdnmg(a)microsoft.com.
> ===========================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
>
> Note: The MSDN Managed Newsgroup support offering is for
> non-urgent issues where an initial response from the community
> or a Microsoft Support Engineer within 1 business day is acceptable.
> Please note that each follow up response may take approximately
> 2 business days as the support professional working with you may
> need further investigation to reach the most efficient resolution.
> The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by
> contacting Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ============================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================
>
>
From: "Charles Wang [MSFT]" on
Hi Prafull,
Metadata cleanup is for publication. Why do you think that it is for
database. You can refer to this article:
sp_mergemetadataretentioncleanup (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms178557(SQL.90).aspx

Metadata cleanup process will call sp_mergemetadataretentioncleanup which
performs a manual cleanup of metadata in the MSmerge_genhistory,
MSmerge_contents, MSmerge_tombstone, MSmerge_past_partition_mappings, and
MSmerge_current_partition_mappings system tables. This stored procedure is
executed at each Publisher and Subscriber in the topology. If you go to see
the tables schema, you can find that there is no database at all. All are
for publication. Anyway you can test to prove this point.

Let us look at your three questions:
1. when the meta data cleanup will happen for above publications?
Answer: After the publication retention peirod arrives.

2. When the metadata will be cleanedup for the subscriber?
Answer: After the publication retention period arrives

3. Can i run the metadata cleanup sp say on day 10? Will this clean meta
for pub7?
Answer: You can mannually call the stored procedure
sp_mergemetadataretentioncleanup on day 10. It will not clean meta data for
pub7. One thing whic is documented in the above article that you may notice
is as following:
==========================
If there are multiple publications on a database, and any one of those
publications uses an infinite publication retention period, running
sp_mergemetadataretentioncleanup does not clean up the merge replication
change tracking metadata for the database. For this reason, use infinite
publication retention with caution. To determine if a publication has an
infinite retention period, execute sp_helpmergepublication (Transact-SQL)
at the Publisher and note any publications in the result set with a value
of 0 for retention.
===========================

Hope this helps.

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg(a)microsoft.com.
===========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================





From: PrafullSurana on
Now you know my scenario, lets me come to specific:

Let us look at your three questions:
1. when the meta data cleanup will happen for above publications?
Answer: After the publication retention peirod arrives.
121108 -- Thanks
2. When the metadata will be cleanedup for the subscriber?
Answer: After the publication retention period arrives
121108 -- Thanks
3. Can i run the metadata cleanup sp say on day 10? Will this clean meta
for pub7?
Answer: You can mannually call the stored procedure
sp_mergemetadataretentioncleanup on day 10. It will not clean meta data for
pub7. One thing whic is documented in the above article that you may notice
is as following:
121108 -- As you see publications in my example have different retention
periods.
a. So as per pub7 retention period , will the metadata be cleanedup after 7
days? though we have another publication with retention period more than 7
days?
b. As per pub14 retention period, metadata cleanup SP will be called after
14 days. Will that clean up data, though we have another publication with
different retention period.
c. When the retention period is set to infinite (for all the publication),
can I call the metadata cleanup SP to clean the data for a perticular period
lets say every month?
d. If answer to c is "no", then what is the use of calling metadata cleanup
SP manually.

I guess i am missing some important point here. Please let me know.

I already went through the part you mentioned and based on that only i came
up with above questions.

My problem is, i can not efford to apply the snapshot for all the subscriber
every 14 - 30 days. And If I keep the retention to infinite, i will have
perfromance problem and database size issue.
So if you have any other suggestion please let me know.

--
Thanks and Regards,
Prafull


""Charles Wang [MSFT]"" wrote:

> Hi Prafull,
> Metadata cleanup is for publication. Why do you think that it is for
> database. You can refer to this article:
> sp_mergemetadataretentioncleanup (Transact-SQL)
> http://msdn.microsoft.com/en-us/library/ms178557(SQL.90).aspx
>
> Metadata cleanup process will call sp_mergemetadataretentioncleanup which
> performs a manual cleanup of metadata in the MSmerge_genhistory,
> MSmerge_contents, MSmerge_tombstone, MSmerge_past_partition_mappings, and
> MSmerge_current_partition_mappings system tables. This stored procedure is
> executed at each Publisher and Subscriber in the topology. If you go to see
> the tables schema, you can find that there is no database at all. All are
> for publication. Anyway you can test to prove this point.
>
> Let us look at your three questions:
> 1. when the meta data cleanup will happen for above publications?
> Answer: After the publication retention peirod arrives.
>
> 2. When the metadata will be cleanedup for the subscriber?
> Answer: After the publication retention period arrives
>
> 3. Can i run the metadata cleanup sp say on day 10? Will this clean meta
> for pub7?
> Answer: You can mannually call the stored procedure
> sp_mergemetadataretentioncleanup on day 10. It will not clean meta data for
> pub7. One thing whic is documented in the above article that you may notice
> is as following:
> ==========================
> If there are multiple publications on a database, and any one of those
> publications uses an infinite publication retention period, running
> sp_mergemetadataretentioncleanup does not clean up the merge replication
> change tracking metadata for the database. For this reason, use infinite
> publication retention with caution. To determine if a publication has an
> infinite retention period, execute sp_helpmergepublication (Transact-SQL)
> at the Publisher and note any publications in the result set with a value
> of 0 for retention.
> ===========================
>
> Hope this helps.
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ===========================================================
> Delighting our customers is our #1 priority. We welcome your
> comments and suggestions about how we can improve the
> support we provide to you. Please feel free to let my manager
> know what you think of the level of service provided. You can
> send feedback directly to my manager at: msdnmg(a)microsoft.com.
> ===========================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================
>
>
>
>
>
>