Thursday, August 25, 2011

Error trying to REORGANIZE/ REBUILD clustered index on CDC Enabled table

Recently I was trying to REORGANIZE a Change Data Capture enabled table when I received the following error:

Msg 22983, Level 16, State 1, Procedure sp_cdc_ddl_event_internal, Line 77
The unique index 'MyTable_PK' on source table 'MyTable' is used by Change Data Capture. To alter or drop the index, you must first disable Change Data Capture on the table.
Msg 3609, Level 16, State 2, Line 2
The transaction ended in the trigger. The batch has been aborted.

This table is very huge - more than 500 million rows. So the reorganize took a very long time - almost 19 hours. I was constantly monitoring the progress of the operation. And just when it was about 98% complete, I got this error. The frustrating part was that I got this error after 19 hours of running this command.

This was on SQL Server 2008 SP1 RTM. The error itself is a bug that was fixed in CU6 for SP1. So we either had to update to CU6 or look for a workaround. The workaround was to either use DBCC REINDEX or to use ALTER INDEX ALL on MyTable REORGANIZE

Obviously updating a Production SQL machine had to be planned for and was not going to happen immediately. And DBCC DBREINDEX would take the table offline - so that was also a no go. So the only option left was ALTER INDEX ALL. This was such a waste of resources as I had recently reorganized all the other indexes on the table. But since it was the only feasible option, I had to use it.

NB: The error does not appear when reorganizing a non-clustered index. Or may it DOES appear when reorganizing a non-clustered index on the unique key that is defined in CDC.

Bug Reference:

Update: After the reorganize errored out, I checked the fragmentation on the index and found that REORGANIZE did do its magic. Thats logical since REORGANIZE does not roll back the work it has already done. And as I have mentioned the error happened at the end of reorganize. So most of the reorganizing was already complete by then.

No comments: