December 31, 2012 at 3:42 am
Hi There
I have a table which is being replicated from Live server. To performance tune on procedure, I created an index on this table and it was created fine. The table is quite big with several million rows. Currently no one is using it except replication. Now when I try to disable or drop that index, it is getting blocked and it doesn't get dropped even after waiting for hours. The reason is that replication continuously block it. Is it that we can disable/drop index on replicated tables? is there a way around. I can't disturb the replication as that is important.
thanks
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
December 31, 2012 at 4:26 am
I got this from BOL:
If a table is in a transactional replication publication, you cannot disable any indexes that are associated with primary key columns. These indexes are required by replication. To disable an index, you must first drop the table from the publication.
Seems I am stuck now as I can't disable the replication or remove that table from publication. But I think MS should issue some sort of warning while creating indexes on such tables that you wont be able to drop them without stopping replication.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply