sp_mergecleanupmetadata
Allows administrators to clean up meta data in the 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 returns information for all publications. The publication must already exist if explicitly specified.
[@reinitialize_subscriber =] 'subscriber'
Specifies whether to reinitialize the Subscriber. reinitialize_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 is used in merge replication and allows administrators to clean up meta data in the MSmerge_contents and MSmerge_tombstone system tables. Although these tables can expand infinitely, in some cases it improves merge performance to clean up the meta data. This procedure can be used to save space by reducing the size of these tables at the Publisher and Subscribers.
By default, the @reinitialize_subscriber parameter is set to true, and all subscriptions are marked for reinitialization. If you set the @reinitialize_subscriber parameter to false, the subscriptions are not marked for reinitialization. Setting the parameter to false should be used with caution because if you choose not to have the subscriptions reinitialized, you must make sure that data at the Publisher and Subscriber is synchronized.
If you want to run sp_mergecleanupmetadata without the subscriptions being marked for reinitialization, you should:
- Synchronize all Subscribers.
- Stop all updates to the publication and subscription databases.
- Execute a merge by running the Merge Agent. It is recommended you use the –Validate agent command line option at each Subscriber when your run the Merge Agent.
- Execute sp_mergecleanupmetadata. After the procedure has run, you can allow users to modify data at the publication and subscription databases again.
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.
Important A backup of the publication database should be performed after a merge meta data clean up has been run. Failure to do so can cause a merge failure after a restore of the publication database.
sp_mergecleanupmetadata fails if there are ongoing merge processes that are attempting to upload changes to the Publisher at the time the stored procedure is invoked. Attempt to run the stored procedure only when all merges have completed, including continuous-mode merges.
The administrator can deactivate the publication and reactivate it after the merge cleanup has completed. Here is sample code that demonstrates how an administrator would accomplish this task.
- Execute this stored procedure at the Publisher. This stored procedure ensures that any continuous-mode merges that are polling for the publication status fail; this deactivates the Publisher.
EXEC central..sp_changemergepublication @publication = 'dynpart_pubn', @property = 'status', @value = 'inactive'- After all the continuous-mode merges have been terminated, execute the following stored procedures. These stored procedures run the meta data cleanup, and then reactivate the continuous-mode merges.
EXEC central..sp_mergecleanupmetadata @publication = 'dynpart_pubn', @reinitialize_subscriber = 'false'
EXEC central..sp_changemergepublication @publication = 'dynpart_pubn', @property = 'status', @value = 'active'
If a merge cleanup is propagated to a Subscriber that is a republisher, and this republisher is not yet inactive, an error is returned explaining that the cleanup of meta data at a republisher could not be performed because of ongoing merge processes.
Permissions
Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_mergesubscription.
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.