Rebuild Index on Published Tables PK?

  • On the publisher database, can you ALTER INDEX REBUILD (ONLINE=ON) the primary key of a replicated table or must you only do a ALTER TABLE REORGANIZE. I am getting conflicting information from different sources. I have heard that if you REBUILD you will break replication. But I don't know if that is true.

    Does anyone have a good source or link that spells out what you can or can't do?

    Thanks in advance for your help!

  • The only thing about replication in the Books Online is about disabling indexes. I've never heard you couldn't rebuild indexes because of replication. I know I used to use the old DBCC REBUILDINDEX when I had replicated databases with no issues. I don't recall if I used the new ALTER INDEX syntax or not. But there's not even a hint of an issue in the BOL, so I'm pretty sure it's OK.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • You can rebuild it.

    What you can't do is drop the primary key, not because it'll break replication, but because you'll get an error if you try.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thank you Grant and Gila!!!

    You guys rock!

  • It's worth noting that rebuilds aren't themselves replicated.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply