Considerations for Managing the Secondary Truncation Checkpoint
After you enable Change Data Capture for at least one Microsoft SQL Server database, Data Replication and SQL Server backups of transaction logs use the secondary truncation checkpoint that is set for the database to identify the last replicated transaction in a transaction log. Data Replication and SQL Server back up only the log records that are marked as replicated.
Only one Data Replication or SQL Server task can manage the secondary truncation checkpoint. The Data Replication task can be an Extractor task or a Microsoft SQL Server Backup task. If multiple tasks try to manage the secondary truncation checkpoint, incomplete data capture might occur.
The Extractor runs the SQL Server sp_repldone procedure to update the secondary truncation checkpoint and mark extracted records as replicated.
The sp_repldone procedure might take a long time to perform this processing if a large number of SQL operations occurred on the source since the last sp_repldone procedure run.
Disable secondary truncation checkpoint management by the Extractor in the following cases:
You run the Extractor that reads transaction logs and SQL Server Change Data Capture (CDC) or native transactional replication against the same database. In this case, SQL Server must manage the secondary truncation checkpoint and produce backup logs.
You run two Extractors that read transaction logs from the same database. Only one Extractor task can manage the secondary truncation checkpoint.
You run the Extractor that reads transaction logs and a Data Replication Microsoft SQL Server Backup task for which the
Run the sp_repldone procedure
option is selected against the same database. Only one task can manage the secondary truncation checkpoint.
If you disable the secondary truncation checkpoint management by the Extractor, ensure that the Extractor reads both the transaction and backup logs to prevent incomplete data capture.
To disable secondary truncation checkpoint management for the Extractor from the Data Replication Console, click the
Runtime Settings
tab >
General
view and select
Disable secondary truncation checkpoint
.
If none of the SQL Server and Data Replication tasks manage the secondary truncation checkpoint, log backups do not free data in the transaction log. This situation causes the transaction log to increase in size. To truncate transaction logs, perform one of the following actions:
Enable secondary truncation checkpoint management for the Extractor and then run the Extractor.
When you create the Microsoft SQL Server Backup task in the Data Replication Console, select the
Run the sp_repldone procedure
option.
Mark the committed data in the transaction logs as "distributed" by running the sp_repldone procedure and then back up the transaction log. To perform these tasks, run the following commands: