Hello,

I am using transactional replication and want to run purge job on the publisher database. Lets say at the end of purge I am rebuilding all indexes only on those tables where i purged the data from. Now if I rebuild the indexes on the publisher database do I need to rebuild the indexes on subscriber too even though I am replicating schema changes?  Also will rebuilding indexes on publisher database or subscriber database break replication (or cause data mismatch?)?

Thanks for your replies.

There is no special instruction needed to be enforced during rebuilding an indexes on publisher database. It will not break replication. However, doing index rebuilding on larger table which creates larger volume of transactions and you can expect a latency in data synchronization. I would recommend to do it during off business hours.

--Prashanth

• Proposed as answer by Lydia ZhangMicrosoft contingent staff, Moderator 5 hours 47 minutes ago

Hi SQLSPUser,


Besides Prashanths post, please note that primary keys are required on tables in transactional publications, so we cannot drop and recreate primary keys on these tables when rebuilding indexes for replication.

Also you need to rebuild indexes at both publisher and subscriber as the rebuilding process is not replicated.

Reference:
Frequently Asked Questions for Replication Administrators
https://msdn.microsoft.com/en-us/library/ms151740.aspx

Fragmentation on Replicated Tables
http://sqlfool.com/2009/01/fragmentation-on-replicated-tables/

Thanks,
Lydia Zhang

If you have any feedback on our support, please click here.