none
Can't get subscription to expire

    Question

  • I am having trouble getting my subscription to expire.  I am using merge replication.  I have a main publisher which has its retention set to 14 days.  Then I have a republisher that I have set its retention period to one day.  When I turn off syncing and wait for 2+ days (1 day for the retention period plus 24 hour grace period) and then turn syncing back on the subscription never expires and everything syncs normally.  Before syncing I have even executed the stored procedure sp_mergemetadataretentioncleanup to force cleanup of the metadata tables but the subscription never expires.  Can someone help out?  Why can't I get my subscription to expire with the retention period? 
    Monday, February 20, 2012 3:52 AM

All replies

  • In the replication monitor click on the subscriptions go to warnings and enable the alert to "warn if a subscription will expire within the threshold". This will show that the subscription is about to get expired as soon as the publisher retention is crossed.

    Now the reason why it is not really expired I guess is because, the distribution retention period might be higher than the publisher retention. Hence even if you manually try to force metadata cleanup it basically doesnt cleanup anything as the distribution retention period is still high. Now that makes the "expiring" subscription to be still not deactivated after 1 day.

    I tried to search all over to see if there is any article which speaks about this situation about keeping publisher retention lower than the distributor retention and its effects. I think that is your case.

    Lets see if others have any view on this.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.wordpress.com

    • Proposed as answer by Peja Tao Tuesday, February 21, 2012 2:58 AM
    • Unproposed as answer by nighttrain79 Tuesday, February 21, 2012 3:18 PM
    Monday, February 20, 2012 12:20 PM
  • If there is no data going through a filtered publication to that subscription it will take twice the retention period + 1 day.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    • Proposed as answer by Peja Tao Tuesday, February 21, 2012 2:58 AM
    • Unproposed as answer by nighttrain79 Tuesday, February 21, 2012 3:18 PM
    Monday, February 20, 2012 3:03 PM
    Moderator
  • So I checked the distributor properties and the transaction retention was marked to store transactions at least 0 hours but not more than 72 hours.  In this case it doesn't seem that the distributor retention is exceeding the publication retention.
    Tuesday, February 21, 2012 2:56 PM
  • In testing in the past I have let a subscription not sync for more than 3 days.  With the publication retention being set at 1 day then what you are saying is that twice the retention plus one day and then the subscription should expire ((2x1)+1) = 3 days.  After letting the subscription not sync for more than 3 days I still invoked the stored proc for cleaning up metadata.  I even made changes to the publisher and subscriber so there would be changed upon restoring the sync.  After doing so the subscription wouldn't expire and everything synced as normal.
    Tuesday, February 21, 2012 3:00 PM
  • Transaction retention and history retention really do not come into play here. It is really the merge system tables which dictate when a subscription expires.

    I am a little confused about how you are trying to generate the expiration. I think you need to let the merge agent run and it is not clear to me you are doing this.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Tuesday, February 21, 2012 3:34 PM
    Moderator
  • I am creating a merge pull subscription at the client.  We created a custom windows service to handle the syncing of our pull subscription.  To get it where Im no longer syncing I just turn off our windows service (the clients are running sql server express 2008 r2 and this service was necessary).  By turning off the service we are no longer syncing and I should just be able to wait until I exceed the retention period plus the 24 hour grace period.  When I turn on my sync service it's just telling the merge pull subscription to sync and it always succeeds, never expires.  This is confusing me because I have exceeded my retention period yet the subscription does not expire.  I found the sp_mergemetadataretentioncleanup and tried using this to force cleanup of the metadata tables before restarting my sync service but doesn't seem to help.

    Tuesday, February 21, 2012 4:51 PM
  • When I created the publication the retention was set to 14 days.  Through SSMS I changed the retention to 1 day.  Do I need to drop and recreate the publication to get the new retention period to take?  Also there are other subscribers, would I need to drop all the subscribers before adjusting the retention period?

    Tuesday, February 21, 2012 9:25 PM