SqlSyncChangeTrackingProvider and unfrequently changed tables

[slawomirpiotrowski]

Hello,

I have an application that is constantly synchronising a table from server to a set of clients (connecting with each of the clients directly by TCP) using DotMim.Sync with SqlSyncChangeTrackingProvider.
Synchronisation with each of the clients works asynchronously to not delay all synchronisations if for example one of the clients is temporary unavailable.

The problem I have is that sometimes I get an exception like this:

2025-10-09 15:27:08.362 Dotmim.Sync.SyncException: Client database is out of date. Last client sync timestamp:2264845. Last server cleanup metadata:2264846 Try to make a Reinitialize sync.

So I do a reinitialisation and a while later again:

2025-10-09 15:51:19.367 Dotmim.Sync.SyncException: Client database is out of date. Last client sync timestamp:2264846. Last server cleanup metadata:2264862 Try to make a Reinitialize sync.

What is the problem? My retention period is set to a fairly long time (4 days exactly) so last server cleanup metadata shouldn't increase that fast. I have tracked the code executed by DotMim.Sync to:

                    DECLARE @maxVersion bigint;
                    SELECT @maxVersion = MAX(CHANGE_TRACKING_MIN_VALID_VERSION(T.object_id)) 
                    FROM sys.tables T 
                    WHERE CHANGE_TRACKING_MIN_VALID_VERSION(T.object_id) is not null;

Initially it works just fine, but after a while, if some of the table are not updated at all, problems arise.

What CHANGE_TRACKING_MIN_VALID_VERSION returns for a table that was not updated during retention period at all? It returns next change tracking timestamp to be used (in the future).

So if at least one of tracked tables didn't have any changes tracked, it causes havoc - any changes to any of the other tables cause a full reinitialisation. And after that any following change to any of the tables cause yet another reinitialisation.

Any ideas how to fix that (other than doing dummy writes to each of rarely updated tables)?

[david-rubie]

We had exactly the same problem - note the difference in timestamps is off by 1 exactly.

{"Client database is out of date. Last client sync timestamp:1468. Last server cleanup metadata:1469 Try to make a Reinitialize sync."}

and yours:
2025-10-09 15:27:08.362 Dotmim.Sync.SyncException: Client database is out of date. Last client sync timestamp:2264845. Last server cleanup metadata:2264846 Try to make a Reinitialize sync.

In our case, the re-sync never finishes because it times out.

I'm not sure why, but this teensy difference in our logic kicks off an entire client Re-init-with-upload. In our case, we catch the exception, update out client side sync number with the number that comes back from the server and the sync works without taking an hour to re-init.

Please note it's REALLY STUPID TO PARSE THE NUMBERS FROM THE EXCEPTION and I'm sure there is a better way to do it, the important thing here is that you bump the LastServerSyncTimestamp on the client to the same value as the serverCleanupMetadata IF they are close enough that it doesn't matter.

In our case pretty much all our clients were exactly 1 off, so this solves a lot of problems for us while we work out what is going wrong.

catch (SyncException ex) when (ex.Message.ContainsIgnoreCase("Client database is out of date"))
{
    parameters.UpdateProgressCallback?.Invoke(new ManualSyncProgress
    {
        ProgressValue = 0,
        Message = "Client database is out of date"
    });
    // parse the exception to get the two timestamps
    nextSyncType = SyncType.ReinitializeWithUpload;
    nextSyncStandardSync = false;
    nextConflictHandler = args => Task.CompletedTask;
        var match = System.Text.RegularExpressions.Regex.Match(
        ex.Message,
        @"Last client sync timestamp:(\d+)\. Last server cleanup metadata:(\d+)"
    );

    if (match.Success)
    {
        int clientSyncTimestamp = int.Parse(match.Groups[1].Value);
        int serverCleanupMetadata = int.Parse(match.Groups[2].Value);
        // Update scope_info_client table to set the last_sync_timestamp to the serverCleanupMetadata value
        // if they are within 10 seconds of each other
        if (serverCleanupMetadata < clientSyncTimestamp + 10)
        {
            var scopeInfoClient = await localOrchestrator.GetScopeInfoClientAsync();
            scopeInfoClient.LastServerSyncTimestamp = serverCleanupMetadata;
            scopeInfoClient = await localOrchestrator.SaveScopeInfoClientAsync(scopeInfoClient);
            nextSyncType = SyncType.Normal;
            nextSyncStandardSync = true;
        }

    }

[slawomirpiotrowski]

Thanks for the confirmation, @david-rubie .

If you want to avoid parsing exception message, you might be able to read those from localOrchestrator and remoteOrchestrator.

But I'm not sure if ignoring off by a few is safe.

You risk problems if your client synchronizes once per retention period. Your tolerance can allow it to skip few modifications. Maybe doing dummy writes (for example once per day) is safer, although more ugly (and might require modifications in the application).

Another option would be to check time of last synchronization of this client (we'd need to store it somewhere) and compare it with current time minus retention period. This way we should be able to check if allowing off by few is safe in given situation or not.

It would be great of DotMim.Sync could make such time check for us. Or at least store somewhere time of last successful synchronization of each client so we could easily add our logic in an OnSomething handler.