Error in job cdc_capture

  • Hi,

    I would like to help to solve the problem in the change data capture messages as below:

    Executed as user: NT AUTHORITY\SYSTEM. Unable to add entries to the Change Data Capture LSN time mapping table to reflect dml changes applied to the tracked tables. Refer to previous errors in the current session to identify the cause and correct any associated problems. [SQLSTATE 42000] (Error 22858) The statement has been terminated. [SQLSTATE 01000] (Error 3621). NOTE: The step was retried the requested number of times (10) without succeeding. The step failed.

    Result set sys.dm_cdc_errors

    error_message:

    - Violation of PRIMARY KEY constraint 'lsn_time_mapping_clustered_idx'. Cannot insert duplicate key in object 'cdc.lsn_time_mapping'.

    - Unable to add entries to the Change Data Capture LSN time mapping table to reflect dml changes applied to the tracked tables. Refer to previous errors in the current session to identify the cause and correct any associated problems.

    I thank

  • Hi,

    Problem solved! 😀

  • Can you elaborate what you did to handle the issue and solve it ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Hi ALZDBA,

    Stop in job_capture and executed the script below:

    select * into bkp_lsn_time_mapping from cdc.lsn_time_mapping

    go

    truncate table cdc.lsn_time_mapping

    go

    Start in job_capture and executed the script below:

    insert into cdc.lsn_time_mapping

    select * from bkp_lsn_time_mapping

    where start_lsn not in (select start_lsn from cdc.lsn_time_mapping).

  • Thank you for the feedback about the remediation. :w00t:

    Did you manage to find the cause for this to happen ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • What SP level are you in? This was a bug when they introduced CDC. But i has been resolved already.

    -Roy

  • Unfortunately I could not find the cause, even using the profiler.

    Versão: 10.0.2531.0

    Service Pack: SP1

    Edição: Enterprise Edition (64-bit)

  • It is a bug on version. When you upgrade your SP, it will go away.

    -Roy

  • We actually ran into this issue this week. Our CDC has been up and running for over a year without any issues. Funny thing is we have two databases that use CDC on the same server. One was unaffected and kept churning away, the other died with this error. We are running version 10.50.2500.0 (x64).

    What version was this 'fixed' in?

    It took us several tries before we could get our CDC to work again. We removed the CDC from the affected tables and re-enabled it. Took 3 times before it actually worked correctly.

    -A.

  • I hit this issue yesterday on SQL 2008 10.0.6000.

    Which version do you think has the fix?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply