How to Manage Truncation Points in SQL Server

Question

How can I manage truncation points in SQL Server?

Environment

SQL Server connector

Answer

SQL Server has multiple truncation points. Truncation points prevent the database transaction log (TLog) from being overwritten. The TLog expands in size to a limit of your choice or until your system runs out of disk space, as long as truncation points prevent overwriting the TLog and the database continues to process transactions. It is crucial to manage these truncation points to prevent your database from suddenly stopping, which could potentially crash the system.

What truncation points can prevent log truncation?

Open transactions and checkpoints are common truncation points that prevent log truncation in a SQL Server database processing transactions. When your database runs in the Full Recovery mode and you have taken at least one full database backup since enabling the Full Recovery mode, log backup becomes an additional truncation point that moves each time you take a backup.

Introducing log-based Change Data Capture (CDC) through SQL Server's native CDC tables or articles (used for native SQL Server replication) creates a truncation point for replication. This truncation point prevents overwriting the log until the changes have been harvested from the log.

At any time, you can query log_reuse_wait_desc from sys.databases to find the current reason why a database TLog cannot be truncated. SQL Server's Factors that can delay log truncation documentation lists the truncation points that may prevent log truncation.

How does Fivetran impact truncation points?

Fivetran's SQL Server connector provides the following options to identify changes on a SQL Server database:

• Fivetran Teleport Sync determines changes between source and target by selecting from the source database. Using Teleport has no effect on truncation points.
• Change Tables are managed as an in-memory row-store that is flushed to disk as part of the SQL Server checkpoint operation. The truncation point for replication is not introduced when using change tables, so you don't need to manage a truncation point.
• SQL Server's native CDC tables feature captures the changes from the online log and applies these to the change tables. This native process moves the truncation point for replication, so you don't need to manage a truncation point.
• SQL Server High Volume Agent (HVA) leverages a binary log reader for a database running in the Full Recovery mode. The impact on the truncation point for replication is similar to HVR. Learn more in our HVR 6 - How to Manage Truncation Points in SQL Server documentation.

Cleanup after CDC change

When you introduce CDC tables or articles in your SQL Server database manually, you implicitly activate the truncation point for replication. If or when you decide to stop using CDC on your database, you must clean up these objects. You can use select log_reuse_wait_desc from sys.databases; to find out why the TLog cannot be truncated if you expect it to be truncated.