none
CDC capture Job fails with error #2601

    Question

  • Hello,

    I use CDC Feature on SQL Server 2012 SP3 (11.0.6251.0) in a replicated environment and suddenly the CDC capture job stopped with the Error 2601:

    Cannot insert duplicate key row in object "cdc.xxx_CT" with unique index "dbo_xxx_CT_clustered_idx". The duplicate key value is (0x001258a6000007a1005a, 0x001258a6000006b1002d, 1).  For more information, query the sys.dm_cdc_errors dynamic management view.

    The DMV doesn't help.

    I'm aware of SP4 update and I intend to apply it. On my test box I was able to reproduce the reported CDC issue (CDC functionality may break ).So, I was able to fix it with the suggested workaround.

    I've never seen the above error and I couldn't find any hint, if the update to SP4 could fix it. Can you give me a clue ?

    Is it possible to reproduce Error 2601 in a test environment in order to test differen scenarios ?

    Any ideas how to solve it ?

    Thanks,

    Bobi

    Tuesday, July 10, 2018 10:39 AM

Answers

  • Hi flopirr,

    We look forward to your feedback after applying SP4.

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact 

    Hi Emily,

    SP4 has fixed the issue. 

    Best Regards

    Bobi

    • Marked as answer by flopirr Saturday, July 14, 2018 4:06 PM
    Saturday, July 14, 2018 4:06 PM

All replies

  • What columns compose of the index "dbo_xxx_CT_clustered_idx? It looks like it missed the column - "__$command_id".
    Tuesday, July 10, 2018 4:23 PM
  • The index is composed of [__$start_lsn], [__$seqval] and [__$operation] columns.

    The _$command_id column is introduced with SP4. I'm currently using SP3 (11.0.6251.0).

    Do I have any chance to verify in my test environment if SP4 would fix the issue ? 

    Tuesday, July 10, 2018 8:30 PM
  • There is a function available to convert the  [__$start_lsn], msn to datetime  please check online, once you convert to DateTime make sure you are not overlapping the time period, That could be the cause of duplicate key value

    mohammad waheed



    Tuesday, July 10, 2018 9:13 PM
  • The index is composed of [__$start_lsn], [__$seqval] and [__$operation] columns.

    The _$command_id column is introduced with SP4. I'm currently using SP3 (11.0.6251.0).

    Do I have any chance to verify in my test environment if SP4 would fix the issue ? 

    Yes, and also I guess SP 3-CU3 is enough to fix that.

    https://support.microsoft.com/en-us/help/3155503

    Download: https://support.microsoft.com/en-us/help/3152635/cumulative-update-3-for-sql-server-2012-sp3

    Tuesday, July 10, 2018 9:41 PM
  • There is a function available to convert the  [__$start_lsn], msn to datetime  please check online, once you convert to DateTime make sure you are not overlapping the time period, That could be the cause of duplicate key value

    mohammad waheed




    Thanks for pointing it out. I'm aware of it, but it is a runtime error.
    Wednesday, July 11, 2018 5:45 PM
  • Thanks for your answer. I'm going to apply SP4 and then I'will post a short feedback

    My point was to reproduce the error in a test environment, apply SP4 and then check if the capture Job would start. So that, I can verify that there is no additional issue to take care of.

    I dont't know which operation have triggered the index violation. 

    Merge statement could be fired either  by .net core, tsql or the user. 


    Wednesday, July 11, 2018 6:04 PM
  • Hi flopirr,

    We look forward to your feedback after applying SP4.

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, July 12, 2018 4:39 PM
  • Hi flopirr,

    We look forward to your feedback after applying SP4.

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact 

    Hi Emily,

    SP4 has fixed the issue. 

    Best Regards

    Bobi

    • Marked as answer by flopirr Saturday, July 14, 2018 4:06 PM
    Saturday, July 14, 2018 4:06 PM