sp_mergecleanupmetadata (Transact-SQL)

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

Transact-SQL Syntax Conventions

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 metadata cleanup. For more information about metadata cleanup, see How Merge Replication Works. 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.

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 is 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 are lost when the publication is reinitialized, but data convergence is maintained.
2. Execute a merge by running the Merge Agent. We recommend that 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 receive a prompt to regenerate the snapshot.
7. Back up 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. We recommend that 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 receive a prompt to regenerate the snapshot.
6. Back up 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.

• 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.

• 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 SQL Server 2000. The Subscribers must be running either SQL Server 2000 or Microsoft SQL Server 7.0, Service Pack 2.

See Also

Reference

MSmerge_genhistory (Transact-SQL)
MSmerge_contents (Transact-SQL)
MSmerge_tombstone (Transact-SQL)

Other Resources

How Merge Replication Works

Help and Information

Getting SQL Server 2005 Assistance