Sync Delays With SQL Server for Linux Using Change Tracking With History Mode Enabled

Issue

When using Change Tracking with history mode enabled in SQL Server for Linux, syncs may have significant delays, ranging from minutes to hours depending on system activity levels.

Environment

• Connectors: SQL Server on Linux (except High-Volume Agent SQL Server)

Resolution

To improve performance and reduce delays, grant necessary permissions to the Fivetran user by running the following command:

USE [master]; GRANT VIEW SERVER STATE TO <username>;

This grants the Fivetran user access to server state information, minimizing the dependency on checkpoint timing and reducing update delays.

For high-activity systems with frequent checkpoints, delays are typically in the order of minutes. However, without this adjustment, delays can extend to several hours for low-activity systems.

Cause

This occurs because history mode relies on certain server state information, which is periodically flushed to disk during database checkpoints. Low-activity systems have less frequent checkpoints, leading to longer delays.