Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/246332

From BetaArchive Wiki
Knowledge Base


BUG: Slow Synchronization on Newly Added Subscriptions to Statically Filtered Publications

Article ID: 246332

Article Last Modified on 10/16/2002



APPLIES TO

  • Microsoft SQL Server 7.0 Standard Edition



This article was previously published under Q246332

BUG #: 56713 (SQLBUG_70)

SYMPTOMS

When a new subscriber is added to a statically filtered publication, the initial merge process should just bulk copy (BCP) the user tables and merge system tables into the subscriber. However, under certain conditions, you may see that the initial merge process takes a long time. The trace files may indicate that the rows from the merge system tables like MSmerge_contents and MSmerge_tombstone are processed although they have already been copied through BCP.

CAUSE

If a new subscriber is added to an existing publication and the new subscriber is added to the publication after the retention period for subscriptions, the merge agent processes statically filtered publications as though they were dynamically filtered publications. The default retention period for subscriptions is 60 days. After the retention period the rows in the MSmerge_genhistory table on the publisher are aged and removed resulting in an erroneous call to sp_MSinitdynamicsubscription and poor performance of the merge job.

WORKAROUND

Follow these steps to work around this behavior:

  1. Unsubscribe all the subscriptions to the publication.
  2. Unpublish the publication.
  3. Mark the published database as unpublished with this code:

    sp_replicationdboption 'publisheddbname','merge publish',false
                            

    The preceding code removes the merge system tables.

  4. Republish.
  5. Resubscribe.

Setting the retention period using the @retention value in the sp_addmergepublication for the subscription expire to be a higher value only delays the manifestation of the problem.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0.


Additional query words: static, dynamic, merge, filters, performance, subscriber

Keywords: kbbug kbpending KB246332