CDC capture Job Failure in sql server 2008 X64 Enterprise Edition

  • Recently I Restored a database from Live to Test Instance for implementing CDC feature on sql server 2008 X64 Enterprise Edition on Windows server 2008r2 X64.

    ***Meant there was NO CDC Enabled in Production Nor Replication Features Its stand Alone.

    Initially i Enabled CDC on Database level and next for 3 Tables, after that i inserted records for all the tables but the same was not getting reflected to CDC tables. then i checked in agent service-> CDC capture Job -> view history, then i got the following below error..

    Message

    Msg 22859, Level 16, State 2, Log Scan process failed in processing log records. Refer to previous errors in the

    current session to identify the cause and correct any associated problems. For more information, query the

    sys.dm_cdc_errors dynamic management view.

    Date 14-Dec-13 8:07:35 AM

    Log Job History (cdc.HC_UAT_DB_V4_capture)

    Step ID 2

    Server ADDCSVHC05

    Job Name cdc.HC_UAT_DB_V4_capture

    Step Name Change Data Capture Collection Agent

    Duration 00:34:29

    Sql Severity 16

    Sql Message ID 22859

    Operator Emailed

    Operator Net sent

    Operator Paged

    Retries Attempted 11

    Message

    Executed as user: Test\hcuat. Could not locate text information records for the column "ExMaritalStatus",

    ID 189 during command construction. [SQLSTATE 42000] (Error 18773) The Log-Scan Process failed to construct a

    replicated command from log sequence number (LSN) {00232390:00001fc9:0015}. Back up the publication database and

    contact Customer Support Services. [SQLSTATE 42000] (Error 18805) Log Scan process failed in processing log

    records. Refer to previous errors in the current session to identify the cause and correct any associated

    problems. [SQLSTATE 42000] (Error 22859)

    can anyone please help me.., what is the cause for the below error and how i can get rid of this.

    "ExMaritalStatus",column -> Nvarchar datatype

    Note :

    *** issue can be solved by disabling & enabling CDC at database level but i need the root cause, again CDC capture job fails for capturing other Nvarchar columns when i Re-Enable at the database level. This cause is getting cycled/looping in capturing other colums.

    Thanks for the advance.

  • Just happen to come across this - its an old thread but it seems that its still relevant.

    You mentioned NTEXT and then NVARCHAR for your column - so it could be the same issue.

    Long short this sounds like the bug we hit about 2 weeks ago which we reported to Microsoft

    It affects all SQL Versions from SQL 2008 to the latest patch level on SQL 2014. The bug specifically affects the SQL Enterprise feature of Change Data Capture (CDC).

    I blogged about it here - https://mrfoxsql.wordpress.com/2015/05/02/new-bug-change-data-capture-cdc-fails-after-alter-column/

    In Summary - The bug will occur when you change a data type from TEXT to VARCHAR(MAX) on a table that is marked for CDC, and you then update any row to push the LoB value off page (ie total row size exceeds the page size). What makes this bug so damn sinister is that you could successfully do the table ALTER and then the problem will sit dormant till whenever you update that LoB column off page.

    When the bug occurs it will manifest itself with a broken CDC Log Reader (ie SQL Agent CDC Capture Job) that will NOT move past a specific LSN.

    If its the same issue - sorry but there are no fixes (yet) - but yes the workaround you mentioned will work temporarily till the next column type change that is.

    We are waiting on a hotfix.

    Thanks - Rolf

    https://mrfoxsql.wordpress.com

    ----------------------------------------
    Rolf T (Mr. Fox SQL)
    https://mrfoxsql.wordpress.com/
    rolf.tesmer@mrfoxsql.com.au
    ----------------------------------------

Viewing 2 posts - 1 through 1 (of 1 total)

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