Microsoft KB Archive/308878

= FIX: sp_mergemetadataretentioncleanup Does Not Clean Generations with ArtNick=0 =

Article ID: 308878

Article Last Modified on 9/26/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Service Pack 1

-



This article was previously published under Q308878



BUG #: 355701 (SHILOH_BUGS)



SYMPTOMS
Merge replication between publishers and subscribers can run into performance problems, with the merge process taking a long time to complete. Looking into the MSmerge_genhistory table, one can observe the presence of an excessive number of rows with &quot;Art_Nick=0&quot;.

Another symptom of the same problem can be the growth of Tempdb, which can increase dramatically (on the order of GBs) depending on the number of Art_Nick 0 rows.



CAUSE
The increase in Art_Nick=0 rows causes the merge process to take a long time because it must process all the rows present in the MSmerge_genhistory table.

The situation is worsened if any interruption occurs during the synchronization phase between the publisher and the subscriber which is undergoing initial synchronization of a nondynamic snapshot. When an interruption occurs, SQL Server localizes the generations at the publisher. Failed application of a nondynamic snapshot can leave a generation with Art_nick = 0 as an interrupted generation. This interrupted generation can later be localized into a new generation. With repeated interruptions and localizations, there can be an excessive number of new generations introduced into the system with Art_Nick 0.

The problem has been aggravated in SQL Server 2000 Service Pack 1 (SP1) where a new stored procedure sp_mergemetadataretentioncleanup performs retention-based cleanup. This new cleanup procedure does not take into effect the art_nick 0 rows and does not clean them up. Before SP1, SQL Server uses sp_MScleanuptask, which cleans up the rows including art_nick 0. Because the old rows with art_nick 0 are left behind in SP1, along with the generation localization problem, merge replication may experience the generation explosion problem.

The problem with Tempdb growth can be attributed to the fact that SQL Server must enumerate all of the rows in the msmerge_genhistory table as part of running the sp_MSenumgenerations stored procedure, which makes use of Tempdb for its processing.



RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

Hotfix
NOTE: The following hotfix was created prior to Microsoft SQL Server 2000 Service Pack 2.

The English version of this fix should have the following file attributes or later:   Date          Time    Version    Size    File name ---  26-SEP-2001   16:34   8.00.451   21 KB   Qfe355701.sql NOTE: Due to file dependencies, the most recent hotfix or feature that contains the above files may also contain additional files.



STATUS
Microsoft has confirmed that this is a problem in Microsoft SQL Server 2000. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.



Why We Need Localization of Generations
Consider a situation where synchronization between the publisher and subscriber is interrupted because of crash at the subscriber. If for some reason the subscriber never comes up, we end up with a generation that is old and not closed. This results in a situation where the synchronization needs to start at the oldest generation and process all the records from that point.

Localization avoids this problem by making a copy of the generation locally at the publisher, closing the interrupted generation, and creating a new generation. In this way, SQL Server is not dependent on the failed subscriber to come up to close its generation. This also improves the performance for merge replication because we do not have to start at an old generation because of an interruption.

Significance of Art_Nick 0
Art_nick 0 refers to rows that belong to the entire publication and not specifically to a given article. Having an Art_nick 0 indicates that these generations were created during the application of the snapshot.

Keywords: kbbug kbfix kbqfe kbsqlserv2000presp2fix kbhotfixserver KB308878

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.