Sample Header Ad - 728x90

_CT records with identical __$seqval

0 votes
1 answer
199 views
There is a CDC enabled table of the following form:
create table dbo.foo (
    id int not null primary key,
    a char(1), 
    b char(1), 
    c char(1), 
    d char(1), 
    e char(1),
    updated datetime not null default getdate(),
    active bit not null default 1
);
go
When querying cdc.dbo_foo_CT, I see the paired records of the following form (_note differences in columns a & updated_): | __$start_lsn | __$end_lsn | __$seqval | __$operation | __$update_mask | id | ⭐a | b | c | d | e | ⭐updated | active | __$command_id | |---|---|---|---|---|---|---|---|---|---|---|---|---|---| | 0x02 | _NULL_ | 0x01 | 1 | 0xFF | 1 | a | b | c | _NULL_ | _NULL_ | 12:00:00 | 1 | 1 | | 0x02 | _NULL_ | 0x01 | 2 | 0xFF | 1 | x | b | c | _NULL_ | _NULL_ | 13:00:00 | 1 | 2 | Given [operations 1 & 2](https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/cdc-capture-instance-ct-transact-sql) are delete & insert respectively, I might otherwise expect this to be a full-row delete-then-reinsert pattern from an app; _but_ there are identical values in _both_ __$start_lsn & __$seqval. When attempting to repro a delete/re-insert in a transaction, the __$seqval still increments AFAICT. Notably a merge command produces the delete/re-insert CDC pattern but still increments the __$seqval as expected. CDC was only recently enabled, and _every_ record in cdc.dbo_foo_CT is a paired record of this form although other CDC enabled tables in the same database do not show this pattern. What could be causing this behavior?
Asked by Peter Vandivier (5678 rep)
Sep 18, 2024, 06:00 PM
Last activity: May 5, 2025, 04:02 PM