Forum Discussion
SQL 2016 SP2 CU12 Change Tracking error
Hello!
I have a table dbo.UserGroup under CT.
Table has primary key with two columns UserInfoID, GroupInfoID, both numeric(35).
There were several operations over that table.
Starting version (change_tracking_current_version) was 2622. Final version was 2713.
We are interesting in operation over the record with UserInfoID = 283887247 and GroupInfoID = 48.
We can see all operations with query (over DAC connection):
select
T.*
,CT.*
from
sys.change_tracking_144719568 as CT
left join sys.dm_tran_commit_table as T on
T.xdes_id = CT.sys_change_xdes_id
where
CT.k_UserInfoID_00000001 = 283887247
and CT.k_GroupInfoID_00000002 = 48
and T.commit_ts >= 2622
order by
CT.sys_change_xdes_id
,CT.sys_change_xdes_id_seq
There are two operations:
Two operations
delete and insert operations with same primary key.
If we read all operations at once with query
select * from changetable(changes dbo.UserGroup, 2622) as T where T.SYS_CHANGE_VERSION <= 2713 and T.UserInfoID = 283887247 and T.GroupInfoID = 48
we will see one update operation (CT merged changes):
But our service read data in a middle of a process. So it made two queries with version ranges from 2622 to 2684 and from 2684 to 2713.
select * from changetable(changes dbo.UserGroup, 2622) as T where T.SYS_CHANGE_VERSION <= 2684 and T.UserInfoID = 283887247 and T.GroupInfoID = 48
select * from changetable(changes dbo.UserGroup, 2684) as T where T.SYS_CHANGE_VERSION <= 2713 and T.UserInfoID = 283887247 and T.GroupInfoID = 48
And we've got:
As u can see, delete operation is missing.
Delivering single insert operation to subscriber server gives expected error of primary key violation.
There are three more CU's for 2016 SQL server. But none of it mentions CT.
I deliver DB backup on 2017 (RTM-GDR) and still getting this error.
I've upgraded it to 2017 (CU22) and error still persists.
Thank you in advance.