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
Other recent topics
