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.

Rebuild replicated indexes

I have a number of databases that are being transactionally replicated from SQL 2000 Enterprise edition publisher to SQL 2005 Enterprise edition subscriber. I have included indexes in the replication. The subscriber database is then accessed and the data de-normalised and aggregated for reporting purposes.

My question is this: I want to periodically re-build the indexes on the publisher and subscriber via an automated task. If I rebuild the indexes on the publisher, will that automatically replicate to the subscriber? Will there be a problem with the "snapshot being out of date", and therefore replication stopping? I run a new snapshot once a day in the small hours of the morning. If there is likely to be a problem with the rebuild throwing the replication out, would it be wise to have the rebuild job running just before the new snapshot is taken?

Any help/advice appreciated.

No, the index rebuild isn't replicated. You would have to rebuild the indexes on the publisher and subscriber separately.|||

Or there is another option is to use @.pre/post_snapshot_script. You can use subscriber index rebuilding actions into a script and set the script as @.pre/post_snapshot_script in publication properties page -->Snapshot or through sql (sp_changepublication/sp_changemergepublication).

For example, as for post snapshot script, Distribution agent/merge agent will run the pre-snapshot script before any of the replicated object scripts when applying the snapshot at a Subscriber. The script is executed in the security context used by the Merge Agent when connecting to the subscription database. Pre-snapshot scripts are not run on SQL Server Mobile Subscribers.

Refer to: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/28a629a1-7374-4614-9b04-279d290a942a.htm

Therefore, once you rebuild index in publisher side, reinitialize sub, sub side index will be rebult through pre/post snapshot script as well.

Hope the above information will help.

-Yunjing

rebuild indexes on replicated db

Ive rebuild the Indexes on a Replicated db weekly. However, it makes the Log
Reader Agent blow up with "cannot exec sp_replcmds" error. It doesnt restart
until done manually. Any ideas on how to fix?
SQL2K SP3
TIA, ChrisR
Stop the log reader. Run dbcc reindex. After rebuilding the indexes, restart
the log reader agent.
The problem is that the rebuilding of the indexes is a logged operation and
this interferes with the log reader.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ChrisR" <bla@.noemail.com> wrote in message
news:%23sUKEPM0EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Ive rebuild the Indexes on a Replicated db weekly. However, it makes the
> Log
> Reader Agent blow up with "cannot exec sp_replcmds" error. It doesnt
> restart
> until done manually. Any ideas on how to fix?
> --
> SQL2K SP3
> TIA, ChrisR
>

Rebuild and Reorganize

will maintenance tasks like rebuilding and reorganizing indexes be replicated in transactional replication, or do i have to setup these management tasks on the subscribers as well?

No. These operations are not replicated.

Peng

|||so if i set these tasks up on the subscriber will it affect replication in a negative way?|||As far as I know, there should be no negative impact. Of course, you need to test on your machines to see if the extra workload affects the performance much.