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:
But the solution, using
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.