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
Last activity: May 5, 2025, 04:02 PM