0

I have setup change data capture, ie. CDC, within SQL server on premises and powered an ETL framework using SSIS to use CDC to permit passing Net changes.

Everything is running fine running the incremental loads every 5 min until it hits the CDC state of "TFREDO..." given any failure prior. Afterwards on the next run the "CDC - Get Processing Range" task notes the error the CDC_State is not well formed:

enter image description here

The only way to overcome this from what I could find:https://www.sqlservercentral.com/forums/topic/cdc-state-is-not-well-formed was noted to manually UPDATE the cdc_state in the state table with a new value:

enter image description here

Is there another way to conform CDC so that it was read this "TFREDO..." state variable and restart on it's own?

Thom A
  • 88,727
  • 11
  • 45
  • 75
Sauron
  • 6,399
  • 14
  • 71
  • 136

1 Answers1

0

In our systems, the problem was compounded by having an overnight job fail on 2 nights in a row (10th and 11th):

"Value TFREDO/CE/0x007810C6000064D00032/TS/2020-08-10T05:02:09.1054697/ of CDC state User::CDC_State is not well structured. Structure must be - logicalName(required)/[CS/<cs-lsn>/](optional)[CE/<ce-lsn>/](optional)[IR<ir-start>/<ir-end>/](optional)/[TS/timestamp/](optional)[ER/<error-message>/](optional).".
"Value TFREDO/CE/0x0078114D000F85B00018/TS/2020-08-11T05:26:27.7456461/ of CDC state User::CDC_State is not well structured. Structure must be - logicalName(required)/[CS/<cs-lsn>/](optional)[CE/<ce-lsn>/](optional)[IR<ir-start>/<ir-end>/](optional)/[TS/timestamp/](optional)[ER/<error-message>/](optional).".

When the second night's run (11th) failed, the original TFREDO/CE/... state got overwritten by the latest state.

I had to

  • look in our SQL Agent error log for the first instance of the error (10th)
  • grab the TFREDO/CE/0x007810C6000064D00032/TS/2020-08-10T05:02:09.1054697/ value
  • change to TFEND/CS/0x007810C6000064D00032/TS/2020-08-10T05:02:09.1054697/
  • write a little UPDATE script to fix it in our CDC_State table:
USE YourDatabase;

SELECT [name]
    ,[state]
FROM YourSchema.CDC_State;

BEGIN TRAN;

    UPDATE s
    SET [state] = 'TFEND/CS/0x007810C6000064D00032/TS/2020-08-10T05:02:09.1054697/'
    FROM YourSchema.CDC_State s
    WHERE [name] = 'CDC_State';

    SELECT [name]
        ,[state]
    FROM YourSchema.CDC_State;

ROLLBACK;
--COMMIT; --...when happy with the above changes.
Oreo
  • 529
  • 3
  • 16