Export (0) Print
Expand All
Expand Minimize

sp_mergecleanupmetadata

SQL Server 2000

Should be used only in replication topologies that include servers running versions of SQL Server prior to SQL Server 2000 Service Pack 1. sp_mergecleanupmetadata allows administrators to clean up meta data in the MSmerge_genhistory, MSmerge_contents and MSmerge_tombstone system tables. This stored procedure is executed at the Publisher on the publication database.

Syntax

sp_mergecleanupmetadata [ [ @publication = ] 'publication' ]
    [ , [ @reinitialize_subscriber = ] 'reinitialize_subscriber' ]

Arguments

[@publication =] 'publication'

Is the name of the publication. publication is sysname, with a default of %, which  cleans up metadata for all publications. The publication must already exist if explicitly specified.

[@reinitialize_subscriber =] 'subscriber'

Specifies whether to reinitialize the Subscriber. subscriber is nvarchar(5), can be TRUE or FALSE, with a default of TRUE. If TRUE, subscriptions are marked for reinitialization. If FALSE, the subscriptions are not marked for reinitialization.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_mergecleanupmetadata should be used only in replication topologies that include servers running versions of SQL Server prior to SQL Server 2000 Service Pack 1. Topologies that include only SQL Server 2000 Service Pack 1 or later should use automatic retention based meta data cleanup. See Merge Meta Data Cleanup in How Merge Replication Works for more information on meta data cleanup. When running this stored procedure, be aware of the necessary and potentially large growth of the log file on the computer on which the stored procedure is running.

Caution  After sp_mergecleanupmetadata is executed, by default, all subscriptions at the Subscribers of publications that have meta data stored in MSmerge_genhistory, MSmerge_contents and MSmerge_tombstone are marked for reinitialization, any pending changes at the Subscriber are lost, and the current snapshot is marked obsolete.

If there are multiple publications on a database, and any one of those publications uses an infinite publication retention period (@retention=0), running sp_mergecleanupmetadata will not clean up the merge replication change tracking meta data for the database. For this reason, use infinite publication retention with caution.

When executing this stored procedure, you can choose whether to reinitialize Subscribers by setting the @reinitialize_subscriber parameter to TRUE (the default) or FALSE. If sp_mergecleanupmetadata is executed with the @reinitialize_subscriber parameter set to TRUE, a snapshot will be reapplied at the Subscriber even if the subscription was created without an initial snapshot (for example, if the snapshot data and schema were manually applied or already existed at the Subscriber). Setting the parameter to FALSE should be used with caution because if the publication is not reinitialized, you must ensure that data at the Publisher and Subscriber is synchronized.

Regardless of the value of @reinitialize_subscriber, sp_mergecleanupmetadata fails if there are ongoing merge processes that are attempting to upload changes to a Publisher or a republishing Subscriber at the time the stored procedure is invoked.

Executing sp_mergecleanupmetadata with @reinitialize_subscriber = TRUE:
  1. It is recommended, but not required, that you stop all updates to the publication and subscription databases. If updates continue, any updates made at a Subscriber since the last merge will be lost when the publication is reinitialized, but data convergence will be maintained.

  2. Execute a merge by running the Merge Agent. It is recommended you use the
    –Validate agent command line option at each Subscriber when you run the Merge Agent. If you are running continuous mode merges, see Special Considerations for Continuous Mode Merges later in this section.

  3. After all merges have completed, execute sp_mergecleanupmetadata.

  4. Execute sp_reinitmergepullsubscription on all subscribers using named or anonymous pull subscription to ensure data convergence.

  5. If you are running continuous mode merges, see Special Considerations for Continuous Mode Merges later in this section.

  6. Regenerate snapshot files for all merge publications involved at all levels. If you try to merge without regenerating the snapshot first, you will receive a prompt to regenerate the snapshot.

  7. Backup the publication database. Failure to do so can cause a merge failure after a restore of the publication database.
Executing sp_mergecleanupmetadata with @reinitialize_subscriber = FALSE:
  1. Stop all updates to the publication and subscription databases.

  2. Execute a merge by running the Merge Agent. It is recommended you use the
    –Validate agent command line option at each Subscriber when you run the Merge Agent. If you are running continuous mode merges, see Special Considerations for Continuous Mode Merges later in this section.

  3. After all merges have completed, execute sp_mergecleanupmetadata.

  4. If you are running continuous mode merges, see Special Considerations for Continuous Mode Merges later in this section.

  5. Regenerate snapshot files for all merge publications involved at all levels. If you try to merge without regenerating the snapshot first, you will receive a prompt to regenerate the snapshot.

  6. Backup the publication database. Failure to do so can cause a merge failure after a restore of the publication database
Special Considerations for Continuous Mode Merges

If you are running continuous-mode merges, you must either:

  • Stop the Merge Agent, and then perform another merge without the
    -Continuous parameter specified,

  • Or deactivate the publication with sp_changemergepublication to ensure that any continuous-mode merges that are polling for the publication status fail:
    EXEC central..sp_changemergepublication @publication = 'dynpart_pubn', @property = 'status', @value = 'inactive'
    • When you have completed step 3 of running sp_mergecleanupmetadata, resume continuous mode merges based on how you stopped them. Either:
      • Add the –Continuous parameter back for the Merge Agent,

      • Or reactivate the publication with sp_changemergepublication:
    EXEC central..sp_changemergepublication @publication = 'dynpart_pubn', @property = 'status', @value = 'active'
Permissions

Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_mergecleanupmetadata.

To use this stored procedure, the Publisher must be running Microsoft® SQL Server™ 2000. The Subscribers must be running either Microsoft SQL Server 2000 or Microsoft SQL Server 7.0, Service Pack 2.

See Also

How Merge Replication Works

MSmerge_genhistory

MSmerge_contents

MSmerge_tombstone

Show:
© 2014 Microsoft