Rebuilding the system merge repl indexes

Hi,

We have a client that has a large (5Gb) database replicated to 13 subscribers, the publisher is Sql 2005, the subscribers are Sql Express. The publication has as few filtered articles too. I have found that after several months of continuous running Replication Monitor is taking a long time to report history on each subscriber.

Do people tend to rebuild the indexes on the system merge replication tables on production servers, or should the standard replication jobs take care of this?

Thanks for your help

Graham

1. What is the retention period?

2. Was the cleanup job run?

|||

The retention period is 14 days for all subscribers.

The Agent history clean uo: Distributor job (publisher and distributor are on the same box), was last run yesterday successfully.

The Distribution Clean up: Distributor job has never been run and it not enabled.

The Replication monitoring refresher for Distributor job has never been run and is not enabled.

|||

1. Did the replication Monitor running all the time (on the subscriber)?

2. If you stop and re-start to launch it, does it still take a long time to refresh "sync status"?

3. if the repl monitor still takes a long time to refresh the sync status, can you turn on the profiler to see which RPC call takes unexpected long execution time? (I suspect the SQL Agent job history continuous to grow)

This posting is provided "AS IS" with no warranties, and confers no rights

|||

Replication does not do any index rebuilding or any defragmentation. It may be a good idea to create a job that does this for you during off-peak hours.

You may also want to monitor what's going on in the background while you're refreshing, replmonitor does make use of temp tables as well and it may have some contention with existing replication metadata tables that we're trying to improve for the next release of Katmai.

|||

Things took at turn for the worse yesterday. I had to reinitialize one of the subscribers because for some reason the publisher had decided to delete its merge meta data (the thing that normally happens when the subscriber does not sync for more than the retention period) dispite it sync'ing the day before. When I did the reinit, it then told me the snapshot was obsolete and I had to re-generate it. When I re-generated the snapshot it failed with a timeout after being stuck at 48% for 30minutes. Then all my other subscribers failed because of the same reason - the snapshot was obsolete.

In the end I tore down replication and re-created the publication and added all the subscribers again (this took all day too).

Now replication monitor is a lot more responsive.

Thanks for everyones help.