Remove unnecessary indexes from Replication
-
Hi,
i'd like to remove some unecessary indexes from my replication:
SQL-Versions:
Publisher: SQL 2008 Standard
Distributor: SQL 2012 Standard
Subscriber: SQL 2008
OS-Versions:
Windows 2008 Standard-Server.
I read about some problems, if I just drop an index wich is replicated to an subscriber database. Is there a possibility to delete/drop an index which is no longer used. The index are very big, and it needs a lot of time, if I reorg or rebuild the index.
kind regards,
Andreas
-
Andreas
I don't understand. If you're talking about dropping an index, why do you need to worry about a reorg or rebuild? What indexes does the table have (clustered/non clustered) and which one are you thinking of dropping? You're doing this on the published database, right? If you look at the article properties, are Copy clustered index and Copy non clustered indexes set to True or False?
John
-
Hi John,
i read this page:
http://technet.microsoft.com/en-au/library/ms171864%28v=sql.105%29.aspx
"It is recommended not to add/drop indexes on replication columns in user tables. This results in synchronization failure because replication columns are treated as system columns. "
And they talk about NOT TO drop an index.
Article properties:
Copy clustered index -> true
Copy nonclustered index -> false
forget the part with the index optimization, the first step I want to just delete the index no longer need.
thanks
-
Andreas, that page is about merge replication with a SQL Server Compact 3.5 subscriber. Is that what you have? If so, I'm afraid I'm a little out of my depth. The only thing I can suggest is to stop replication, drop the index and re-run the snapshot. Since your non clustered indexes are not copied, that should get rid of the index from the subscriber.
John
-
ok,
there are many articles on, I will rebuild it in the QA environment, and test.
thanks for your help, when I discovered it, I post it here in the topic
-
If you want to drop an index at the subscriber, go to the subscriber and drop the index. Make sure that you don't adversely affect the subscriber's workload by doing so.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply