Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rebuild Index on Published Tables PK? Expand / Collapse
Author
Message
Posted Wednesday, July 9, 2014 9:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 11:53 AM
Points: 5, Visits: 31
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!
Post #1590827
Posted Wednesday, July 9, 2014 11:52 AM This worked for the OP Answer marked as solution


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:03 PM
Points: 15,729, Visits: 28,132
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1590887
Posted Wednesday, July 9, 2014 11:58 AM This worked for the OP Answer marked as solution


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:25 PM
Points: 43,008, Visits: 36,164
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 2008, MVP
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

Post #1590891
Posted Wednesday, July 9, 2014 12:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 11:53 AM
Points: 5, Visits: 31
Thank you Grant and Gila!!!

You guys rock!
Post #1590900
Posted Wednesday, July 9, 2014 1:39 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:25 PM
Points: 43,008, Visits: 36,164
It's worth noting that rebuilds aren't themselves replicated.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1590929
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse