I am log shipping a database with Change Data Tracking. Data changes made to the primary show up on the secondary using a read-only queries. The SYS_CHANGE_VERSION value on the secondary does not match the values on the primary. Actually, this values does eventually change although I am unable to identify let alone control the cause of the latency. Sometimes the row version is correct sometimes after a few hours. How can I ensure the CDT state is current?

The following illustrate this. BTW The backup, copy, and restore jobs are scheduled every minute.

Starting state.

Item	Primary	Secondary
1 - last_backup_file   (log_shipping_monitor_primary)	MyDb_20150902215200.trn
2 - last_copied_file   (log_shipping_monitor_secondary)		MyDb_20150902215200.trn
3 - last_restored_file   (log_shipping_monitor_secondary)		MyDb_20150902215100.trn
4 -   CHANGE_TRACKING_CURRENT_VERSION()	45	45
5 -   CHANGE_TRACKING_MIN_VALID_VERSION()	31	31
6 - SYS_CHANGE_VERSION FROM   CHANGETABLE(CHANGES)	45	45
7 - Value in table row	48	48

Update a column in one table row on the primary from value of 48 to 49.

3  minutes later. Value updated but not table version.

Item	Primary	Secondary
1 - last_backup_file   (log_shipping_monitor_primary)	MyDb_20150902215701.trn
2 - last_copied_file   (log_shipping_monitor_secondary)		MyDb_20150902215701.trn
3 - last_restored_file   (log_shipping_monitor_secondary)		MyDb_20150902215600.trn
4 -   CHANGE_TRACKING_CURRENT_VERSION()	45	46
5 -   CHANGE_TRACKING_MIN_VALID_VERSION()	31	31
6 - SYS_CHANGE_VERSION FROM   CHANGETABLE(CHANGES)	46	45
7 - Value in table row	49	49

5 minutes later.

Item	Primary	Secondary
1 - last_backup_file   (log_shipping_monitor_primary)	MyDb_20150902215901.trn
2 - last_copied_file   (log_shipping_monitor_secondary)		MyDb_20150902215901.trn
3 - last_restored_file   (log_shipping_monitor_secondary)		MyDb_20150902215801.trn
4 -   CHANGE_TRACKING_CURRENT_VERSION()	46	46
5 -   CHANGE_TRACKING_MIN_VALID_VERSION()	31	31
6 - SYS_CHANGE_VERSION FROM   CHANGETABLE(CHANGES)	46	45
7 - Value in table row	49	49

15 minutes later.

Item	Primary	Secondary
1 - last_backup_file   (log_shipping_monitor_primary)	MyDb_20150902220900.trn
2 - last_copied_file   (log_shipping_monitor_secondary)		MyDb_20150902220900.trn
3 - last_restored_file   (log_shipping_monitor_secondary)		MyDb_20150902220800.trn
4 -   CHANGE_TRACKING_CURRENT_VERSION()	46	46
5 -   CHANGE_TRACKING_MIN_VALID_VERSION()	31	31
6 - SYS_CHANGE_VERSION FROM   CHANGETABLE(CHANGES)	46	45
7 - Value in table row	49	49

3 hours later - notice the table version changed.

Item	Primary	Secondary
1 - last_backup_file   (log_shipping_monitor_primary)	MyDb_20150903010200.trn
2 - last_copied_file   (log_shipping_monitor_secondary)		MyDb_20150903010200.trn
3 - last_restored_file   (log_shipping_monitor_secondary)		MyDb_20150903010100.trn
4 -   CHANGE_TRACKING_CURRENT_VERSION()	46	46
5 -   CHANGE_TRACKING_MIN_VALID_VERSION()	32	32
6 - SYS_CHANGE_VERSION FROM   CHANGETABLE(CHANGES)	46	46
7 - Value in table row	49	49

• Edited by Bruce-K Thursday, September 03, 2015 1:27 AM Removed repeated HTML title from body

It is as expected. As sometimes secondary may not immediately  sync with Primary

• Edited by Cheer08 23 hours 22 minutes ago

It is as expected. As sometimes secondary may not immediately  sync with Primary

• Edited by Cheer08 Friday, September 04, 2015 7:47 AM

The log ship "sync" has already occurred multiple times and the data value changed but not the CDT value. Are you saying there is a "sync" mechanism other than the transaction log backup, copy, and restore? I do expect "sync" latency for the time it takes to:

1. Take the transaction log backup
2. Copy the transaction log backup
3. Restore the transaction log backup

In this application, each of the above steps (jobs) are on a 1 minute schedule and the data shows that all the steps have been executed multiple times since the change on the primary- note the date/time portion of the transaction log filenames. The secondary shows the DML change has been applied to the secondary by the restored transaction log but not the associated CDT version. If the transaction log process ("sync") has completed - why is secondary showing the updated value for the data table but not for the "SYS_CHANGE_VERSION_FROM CHANGETABE(CHANGES)" value?