The main tables for merge replication are MSmerge_contents, MSmerge_tombstone, and MSmerge_genhistory.  In addition to these, there is a set of tables in distribution.  For merge replication, distribution is NOT important.   It simply contains status messages such as: agent started, agent stopped, agent retrying, etc.  Don't bother to backup distribution and if you lose it, simply recreate it.  MSmerge_tombstone contains a row for each row that was deleted from a published table.  Each publication is configured with a retention period.  The clean up jobs will remove anything older than this retention period.  This applies to the messages in distribution and the rows in MSmerge_tombstone ONLY.   It does not apply to anything else.  When you issue a transaction against a published table, a trigger fires and makes entries in the merge metadata tables.   Inserts and updates go into MSmerge_contents.  All new rows get a generation of 0 (zero).  MSmerge_genhistory is the keystone to the merge system and is what provides the power and flexibility.  It contains a row for each row modified in each article in each publication.  When the merge agent fires, it grabs all of the rows with a generation of zero.  It then looks in the MSmerge_genhistory table and gets the highest generation value applied.  It then sets the generation to the next highest value based on what was returned from MSmerge_genhistory and sends the rows to the publisher/subscriber.  Once they are received, a row for each row in each article sent in that generation is inserted into MSmerge_genhistory on the receiving server.   If the row is already tracked in MSmerge_genhistory, the generation is updated.   (For those purists it is a little more complicated than this, but this simplification is close enough to understand what we are doing here and why.)

The last column in MSmerge_genhistory is called coldate.  It is this date that we will use to manage the merge metadata.  The coldate tells you when the last time a modification was made to each row touched in each article.  If you make 500 modifications to the same row, MSmerge_genhistory and MSmerge_contents will only contain one row for all 500 transactions.  The row in MSmerge_genhistory will contain the generation of the last change and the time of the last change.  MSmerge_contents will also contain 1 row for all 500 modifications.

This gives you an idea of why things slow down as MSmerge_contents and MSmerge_genhistory increase in size.  Every time the merge agent fires off, it has a lot of work to do.  It has to find the highest generation, update the generation value with the next highest number, transfer the changes to the publisher/subscriber, search MSmerge_genhistory for matching rowguids for the rows that were transferred and update these, insert rows into MSmerge_genhistory for any rows that were touched for the first time, insert rows into MSmerge_contents for any rows touched for the first time, and update the generation and lineage for any rows that were touched previously.  That is a huge amount of work.  By purging old metadata, this speeds the process by reducing the amount of data the merge agent has to wade through.   This also reduces the likelihood of having deadlocks during all of this processing.

The metadata can literally save you if you ever need to restore and older version of the database.  This means you have to be extremely careful in what you purge and what you don't.

I try to keep the size of MSmerge_contents down to a few hundred thousand rows with a corresponding reduction in MSmerge_genhistory.  This is usually done by only keeping the last 2 weeks worth of generations.  The script you can download here contains the tables and stored procedures that I use to maintain MSmerge_contents and MSmerge_genhistory to keep the replication system healthy.

All systems that I work on contain a database called admin. The sole purpose of this database is to contain all of the administrative procedures, views, and tables that I use.  Some people use master for this purpose.  I strongly discourage that.  You are creating user objects that belong in a user database and NOT in a system database.  Not only does this make restoring very difficult, but it introduces an element of instability for your systems.  The argument of using sp_ as a system procedure doesn't wash, because you can write something that operates globally with just a little more effort.

Since many of you won't have an admin database, the script starts off by creating one.  It then creates two tables

One procedure, sp__mergepurgemetadata is created.  This procedure will purge old metadata based upon the retention period you pass to it.

This procedure comes with the same caveats that the procedure provided in SP2 will come with.  The metadata is extremely useful in a restore stage.  You also must ensure that everything has come across.  I would recommend only removing rows older than 2 weeks.  This will generally ensure that the rows have been propagated to all subscribers.  If you have mobile users, you will want to account for this as well.   If you purge metadata that has not been replicated, those transactions will be lost.  If you have to restore a backup older than the metadata you are carrying in the database, you will not be able resynch that backup.

That is the reason for the mergepurgeflag table.  You explicitly set this flag to 1 only when you are certain all of the transactions within the period you are purging have been replicated.  Not only should you ensure that all subscribers and publishers have merged since that time, but you also need to verify your backups.  If you are going to purge everything older than 2 weeks, make certain you have taken one of the backups that is more recent than the date you are going to purge, restore it to another machine, and verify the integrity of the media, backup, and data.  Only after you have met these two criteria should you set the purgeflag to allow the metadata to be purged.

The script provided can help you maintain the metadata in your system and keep merge replication running efficiently.  There are 2 very large cautions in using this procedure.

1. You must ensure that all publishers and subscribers have successfully merged after the date you are going to purge.
2. You must ensure you have an intact backup after the date you are going to purge.

You you adhere to these restrictions, you will not see any problems.  If you don't, then you will encounter problems that are not going to be easy or nice to clean up.   In either case, the script provided is provided with the same restrictions that I will not be responsible for any system that this is run on, nor will I be responsible for any damage or loss this script may cause.  Use this script at your own risk and only after you fully understand what you are doing and what its effect will or can be.