Diasbling index on replicated table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Diasbling index on replicated table

Diasbling index on replicated table

Rate Topic

Display Mode

Topic Options

Author

Message

S_Kumar_S

Posted Monday, December 31, 2012 3:42 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 10:10 AM
Points: 322, Visits: 720

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

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle

Post #1401348

S_Kumar_S

Posted Monday, December 31, 2012 4:26 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 10:10 AM
Points: 322, Visits: 720

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.

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle

Post #1401354

« Prev Topic | Next Topic »

Permissions