I faced some interesting behaviour with CDC mechanism. I have a table (for example table 'A'), and i've turned on capture changes on it. So in result I have a CDC table 'cdc.dbo_A_CT' that contains captured data. Table contains a lot of columns but two of them are 'id' and 'timestamp', id is incremental (e.g. every new insert receive the next id), timestamp - is operation perfromed datetime. In a few days of table working, I decided to check size of CDC table and original. CDC has a few rows less than original table. In original table I have this data:

  id          timestamp
OP1332    2018-08-06 12:32:28
OP1333    2018-08-06 12:38:42
OP1334    2018-08-06 12:47:12

In CDC table I have only information about inserting:

OP1332 and OP1334

and no data for id = OP1333.

This issue repeat for random table and random time, and have no idea whats going wrong with CDC mechanism.

Enviroment: Microsoft SQL Server 2016 (SP1-CU5) (KB4040714) - 13.0.4451.0 (X64)

  • How exactly are you querying the capture table? – Aaron Bertrand Aug 6 '18 at 14:22
  • I do select * from cdc.dbo_A_CT – Yura Chegodaev Aug 6 '18 at 14:48
  • I can't reproduce this scenario, so I think we'll have to know more about the problem. What CDC settings are you using? Does your table have a primary key? What schema changes have been made while CDC has been enabled? What recovery model? Is SQL Server Agent ever stopped / restarted during normal activity? Do you periodically do anything with the transaction log (like truncate / shrink / change recovery model)? – Aaron Bertrand Aug 6 '18 at 14:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.