Home Forums SQL Server 2012 SQL Server 2012 - T-SQL How can I force a deferred update to create two rows in change data capture? RE: How can I force a deferred update to create two rows in change data capture?

  • which issue is occuring. the deferred updates or the unique constraint violation.

    In my case it was unique constraint violation on CT table though. the CDC capture job was having issue with handling the deferred update scenario. I had opened a case with MS and they recommended applying a hotfix to resolve the issue. However, in my case I was able to avoid the situation by making some code changes in MERGE statement to avoid updating the UNIQUE constraint on the table thereby avoiding deferred updates.

    We had a PK on identity column and unique constraint on one table that was in CDC. when the MERGE ran and was updating the unique key columns (even though the value did not really change) caused deferred updates. We changed the code to avoid update on the unique key columns. Since then no issues are observed.

    I was recommended by MS support to update SQL server to following version:

    SQL Server 2012 SP3 CU3 (11.0.6531.5)