FIX: sp_mergemetadataretentioncleanup Does Not Clean Generations with ArtNick=0 (308878)

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 "Art_Nick=0".

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.

MORE INFORMATION

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.