SQL 2016 SP2 CU12 Change Tracking error

%3CLINGO-SUB%20id%3D%22lingo-sub-1897739%22%20slang%3D%22en-US%22%3ESQL%202016%20SP2%20CU12%20Change%20Tracking%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1897739%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20table%26nbsp%3Bdbo.UserGroup%20under%20CT.%3C%2FP%3E%3CP%3ETable%20has%20primary%20key%20with%20two%20columns%26nbsp%3BUserInfoID%2C%20GroupInfoID%2C%20both%20numeric(35).%3C%2FP%3E%3CP%3EThere%20were%20several%20operations%20over%20that%20table.%3C%2FP%3E%3CP%3EStarting%20version%20(change_tracking_current_version)%20was%26nbsp%3B2622.%20Final%20version%20was%26nbsp%3B2713.%3C%2FP%3E%3CP%3EWe%20are%20interesting%20in%20operation%20over%20the%20record%20with%26nbsp%3BUserInfoID%20%3D%26nbsp%3B283887247%20and%20GroupInfoID%20%3D%2048.%3C%2FP%3E%3CP%3EWe%20can%20see%20all%20operations%20with%20query%20(over%20DAC%20connection)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3Eselect%0A%20T.*%0A%20%2CCT.*%0Afrom%0A%20sys.change_tracking_144719568%20as%20CT%0A%20left%20join%20sys.dm_tran_commit_table%20as%20T%20on%0A%20%20T.xdes_id%20%3D%20CT.sys_change_xdes_id%0Awhere%0A%20CT.k_UserInfoID_00000001%20%3D%20283887247%0A%20and%20CT.k_GroupInfoID_00000002%20%3D%2048%0A%20and%20T.commit_ts%20%26gt%3B%3D%202622%0Aorder%20by%0A%20CT.sys_change_xdes_id%0A%20%2CCT.sys_change_xdes_id_seq%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20two%20operations%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Pic1.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F234231i07E921431412D25F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Pic1.PNG%22%20alt%3D%22Two%20operations%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ETwo%20operations%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Edelete%20and%20insert%20operations%20with%20same%20primary%20key.%3C%2FP%3E%3CP%3EIf%20we%20read%20all%20operations%20at%20once%20with%20query%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3Eselect%20*%20from%20changetable(changes%20dbo.UserGroup%2C%202622)%20as%20T%20where%20T.SYS_CHANGE_VERSION%20%26lt%3B%3D%202713%20and%20T.UserInfoID%20%3D%20283887247%20and%20T.GroupInfoID%20%3D%2048%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewe%20will%20see%20one%20update%20operation%20(CT%20merged%20changes)%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Pic2.PNG%22%20style%3D%22width%3A%20953px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F234223iDF3DDD6EFDC05403%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Pic2.PNG%22%20alt%3D%22Pic2.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EBut%20our%20service%20read%20data%20in%20a%20middle%20of%20a%20process.%20So%20it%20made%20two%20queries%20with%20version%20ranges%20from%202622%20to%202684%20and%20from%26nbsp%3B2684%20to%202713.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3Eselect%20*%20from%20changetable(changes%20dbo.UserGroup%2C%202622)%20as%20T%20where%20T.SYS_CHANGE_VERSION%20%26lt%3B%3D%202684%20and%20T.UserInfoID%20%3D%20283887247%20and%20T.GroupInfoID%20%3D%2048%0Aselect%20*%20from%20changetable(changes%20dbo.UserGroup%2C%202684)%20as%20T%20where%20T.SYS_CHANGE_VERSION%20%26lt%3B%3D%202713%20and%20T.UserInfoID%20%3D%20283887247%20and%20T.GroupInfoID%20%3D%2048%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20we've%20got%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Pic3.PNG%22%20style%3D%22width%3A%20954px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F234225iBA9A58891A8DB95F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Pic3.PNG%22%20alt%3D%22Pic3.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAs%20u%20can%20see%2C%20delete%20operation%20is%20missing.%3C%2FP%3E%3CP%3EDelivering%20single%20insert%20operation%20to%20subscriber%20server%20gives%20expected%20error%20of%20primary%20key%26nbsp%3Bviolation.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20three%20more%20CU's%20for%202016%20SQL%20server.%20But%20none%20of%20it%20mentions%20CT.%3C%2FP%3E%3CP%3EI%20deliver%20DB%20backup%20on%26nbsp%3B2017%20(RTM-GDR)%20and%20still%20getting%20this%20error.%3C%2FP%3E%3CP%3EI've%20upgraded%20it%20to%26nbsp%3B2017%20(CU22)%20and%20error%20still%20persists.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1897739%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Echange%20tracking%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Esql%202016%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Frequent Visitor

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 operationsTwo 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):

Pic2.PNG

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:

Pic3.PNG

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.

0 Replies