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)