question

pits avatar image
pits asked

Error while attempting ALTER INDEX

Good Day, In sql 2008 While executing query "ALTER INDEX [abb_PrimaryKey_Account] ON [dbo].[TT." failed with the following error: " The unique index 'abb_PrimaryKey_Account' on source table '[dbo].[trax]' is used by Change Data Capture. To alter or drop the index, you must first disable Change Data Capture on the table. The transaction ended in the trigger. The batch has been aborted.". Possible failure reasons: Problems with the query, property not set correctly, parameters not set correctly, or connection not established correctly. Error Number : -1073548784 Please advise how we can avoid the same error? Rgds,
errorchange-data-capture
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
It largely explains itself. You have Change Data Capture (CDC) running on that table and you'll have to disable it prior to making modifications to the primary key.
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
You just need to query/filter out tables with CDC enabled. There is a column in sys.tables to show you if a table has CDC enabled or not. SELECT name, is_tracked_by_cdc FROM sys.tables
3 Likes 3 ·
pits avatar image pits commented ·
Is there any way apart from disable the same,is there any script which can identify and excludes those objects? Rgds,
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
@WilliamD Excellent. I was going to ahve to go look that one up.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.