drop index on subscriber.

Question

we have a transaction replication that with 1 publisher and 2 subscribers. I have seen at a times subscribers lag behind in data sync. it happens for only 1 table, this table is sized around 100G and index size is around 200G. im sure there are lots of unwanted indexes on the tables. is there any way i can drop indexes on subscriber without affecting replication. Any step by step article to do it will be of big help.

Answer 1

Cathy,
when you say "so if you add or change an index at, for example, the Publisher, you must make the same addition or change at the Subscriber if you want it reflected there."
But what if the option in article properties that say "copy nonclustered indexes" set to true. in that case shouldnt nonclustered indexes get copied automatically to subscriber ?

Answer 2

Hi @SQLServerBro,

Set the Copy nonclustered indexes value for articles to True. This action will ensure that nonclustered indexes are included whenever the snapshot of the tables is generated. This means that when you do a re-initialization is that when a new snapshot is re applied at the subscriber database ,the non clustered indexes would get copied over.

I test this in my environment, when the Copy nonclustered indexes value for articles to True, the nonclustered indexes will not be copied automatically to subscriber, unless you re-initialize the replication using a new snapshot, this process will make a new snapshot, this snapshot include nonclustered indexes.

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.

Answer 3

Thank you so much for that explaination.