We are using a change tracking solution to export increments for BI purposes for years now. But it was running directly on the production database. To lower the load on the system we wanted it to run on a separate set of databases.

We created these databases by restoring backups and adding transaction log backups. The databases are in standby mode, so readonly.

After switching to these new databases we started seeing differences after processing the increments and checking counts with total counts we provide with the increments.

The differences are usually just a few records per table, but on tables with millions of rows that mutate frequently in the thousands. And the transaction logs backups are done every 15 minutes. And the changes appear quite a bit smaller than the contents of one of these backups.

So we had the feeling this has something to do with non-committed transactions.

We found this, which explains a similar issue:

Change Tracking Current Version Is Not Always Reliable

But the solution, using MAX(commit_ts) from sys.dm_tran_commit_table instead of CHANGE_TRACKING_CURRENT_VERSION() doesn't seem to solve the differences.

Since the changetracking functions also seem to use the current version internally.

After appending another transaction log backup the previously returned changetracking version seems to be consistent again with the data. Also bringing the databases online, fixes the issue as well.

Bringing the databases online is not an option since we would need to do full restores again to get the data up to date, every day. And the performance is quite bad on a freshly restored databases, compared to the current situation.

The question is, how do we get the last consistent change tracking version from a database in standby mode.

  • Just as a curiosity, if you create a snapshot of the database and check the change tracking version there do you get the correct information?
    – Nic
    Apr 11, 2017 at 14:35
  • We tried this as well, it still has the same problem.
    – LeonG
    Apr 11, 2017 at 14:41
  • 1
    Seems that logshipping isn't going to play for you there in that case. Have you considered spinning an up asynchronous AG or mirror for the databases and using a snapshot from the replica to do this?
    – Nic
    Apr 11, 2017 at 15:22


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.