Metadata cleanup - Merge publication

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 RemoveThis @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.
> =========================================================
>
>
 >> Stay informed about: Metadata cleanup - Merge publication 

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.DeleteThis@microsoft.com.
===========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
 >> Stay informed about: Metadata cleanup - Merge publication 

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.RemoveThis@microsoft.com.
> ===========================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================
>
>
>
>
>
>
 >> Stay informed about: Metadata cleanup - Merge publication 

Hi Prafull,
Please see my answers below:
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?

Answer: No. For more than one publication, the longest retention period is
always used to determine when metadata can be removed.


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.

Answer: Yes. If the retention period for your another publicatin is less
than 14 days.

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?

Answer: No. 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.

d. If answer to c is "no", then what is the use of calling metadata cleanup
SP manually.

Answer: The problem here is "infinite". If you set infinite retention
period, mannual running sp_mergemetadataretentioncleanup is not helpful in
this case.

Considering the specific environment, I recommend that you first perform a
test at your side to check what everything is going on before you put it
into your production environment.


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.TakeThisOut@microsoft.com.
===========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
 >> Stay informed about: Metadata cleanup - Merge publication 

Hi Charles,

Thanks a lot for providing feedback.

I hope this will be last set of queries, in this regard:
Based on clarification what i am getting is ,
"sp_mergemetadataretentioncleanup" can not be used the meta data before the
retention period is reached. ( Let it be days, months, years or even
infinity).

What is other way to clean the meta data apart from
"sp_mergemetadataretentioncleanup"?

As I want to avoid application of snapshot at each susbscriber even though
their database in sync with the server. If above constraint was not there
then i was good with retention period use.

--
Thanks and Regards,
Prafull


""Charles Wang [MSFT]"" wrote:

> Hi Prafull,
> Please see my answers below:
> 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?
>
> Answer: No. For more than one publication, the longest retention period is
> always used to determine when metadata can be removed.
>
>
> 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.
>
> Answer: Yes. If the retention period for your another publicatin is less
> than 14 days.
>
> 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?
>
> Answer: No. 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.
>
> d. If answer to c is "no", then what is the use of calling metadata cleanup
> SP manually.
>
> Answer: The problem here is "infinite". If you set infinite retention
> period, mannual running sp_mergemetadataretentioncleanup is not helpful in
> this case.
>
> Considering the specific environment, I recommend that you first perform a
> test at your side to check what everything is going on before you put it
> into your production environment.
>
>
> 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.RemoveThis@microsoft.com.
> ===========================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================
>
>
>
>
>
>
 >> Stay informed about: Metadata cleanup - Merge publication 

Hi Prafull,
Please see my answers as following:
Q: What is other way to clean the meta data apart from
"sp_mergemetadataretentioncleanup"?
A: No. Using sp_mergemetadataretentioncleanup is the only way that we
recommended to cleanup the meta data since it is complete. I think that you
need to consider designing an acceptable maximum retention period for all
of your subscribers.

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 DeleteThis @microsoft.com.
===========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
 >> Stay informed about: Metadata cleanup - Merge publication 

Thanks Charles.

--
Thanks and Regards,
Prafull


""Charles Wang [MSFT]"" wrote:

> Hi Prafull,
> Please see my answers as following:
> Q: What is other way to clean the meta data apart from
> "sp_mergemetadataretentioncleanup"?
> A: No. Using sp_mergemetadataretentioncleanup is the only way that we
> recommended to cleanup the meta data since it is complete. I think that you
> need to consider designing an acceptable maximum retention period for all
> of your subscribers.
>
> 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 RemoveThis @microsoft.com.
> ===========================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================
>
>
>
 >> Stay informed about: Metadata cleanup - Merge publication