How to manage truncation points in SQL Server?

[mark-vandewiel]

SQL Server has multiple truncation points. Truncation points prevent the database transaction log (TLog) from being overwritten. The TLog will expand in size up to a limit you set, or until your system runs out of disk space, if truncation points prevent overwriting the TLog and the database continues to process transactions. Needless to say, you want to avoid your database to suddenly stop processing or worse, crash, and possibly crash the system.

What truncation points can prevent log truncation?

Truncation points that commonly prevent log truncation on a SQL Server database processing transactions include open transactions and checkpoints. When your database runs in full recovery mode, and you have taken at least a full database backup since enabling full recovery mode, then log backup becomes an additional truncation point that moves each time you take a backup.

When introducing log-based Change Data Capture (CDC) through the use of SQL Server's native CDC tables, or through articles (used for native SQL Server replication) then a truncation point for replication is introduced. This truncation point prevents overwriting the log until changes have been harvested from the log.

At any point in time you can query log_reuse_wait_desc from sys.databases to find out the current reason why a database TLog cannot be truncated. The SQL Server documentation lists the truncation points that may prevent log truncation.

How does Fivetran impact truncation points?

Fivetran provides two different options to identify changes on a SQL Server database: using Change Tables (CT) or using native CDC.

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, and you don't have to worry about managing a truncation point.

SQL Server's native CDC tables feature will capture the changes from the online log and apply these to the change tables. When done this native process will move the truncation point for replication. I.e. when using Fivetran with CDC tables you also do not have to worry about managing a truncation point.

How does Fivetran's HVR Solution impact truncation points?

The HVR Solution has its own logic to parse transaction information from the transaction log with a so-called binary log reader. The use of such binary log reader has minimal impact on database processing. However, to perform change data capture for use of the data in a heterogeneous environment you do want to include the row identifier (primary or unique key).

By default SQL Server does not write the application-level row-identifier to the log for updates. However, when either articles (as part of native SQL Server replication) or native CDC tables are in place, then such extra information is written to the log. Hence to use the HVR Solution you must either have articles or CDC tables on top of the base tables.

Note articles and CDC tables each introduce restrictions on DML operations and you want to pick what is right for you. The HVR Solution defaults to the use of CDC tables.

The HVR Solution was built to provide flexible change data capture/data replication for the highest volume environments. We wanted the least possible impact on database processing. Since we wrote a binary log reader we don't require the database to do any change data capture processing. When articles are enabled we don't need changes in a distribution database, avoiding unnecessary database processing and storage/cleanup requirements. Likewise when CDC tables are in place we do not need CDC tables to be populated, again saving on database processing and storage requirements. As a result, with a default configuration, the HVR activate operation for SQL Server will delete the SQL Server Agent job that performs these tasks, limiting the overhead that is introduced to SQL Server.

Please note that the HVR Solution can be configured to coexist with other data replication solutions (either native SQL Server replication or other third-party solutions). In such cases we cannot make any assumptions about the need for the SQL Server Agent jobs and leave these in place. For more information refer to the HVR documentation.

With no SQL Server Agent job in place to manage harvesting changes from the TLog and moving the truncation point for replication, it is important something else moves this truncation point. We recommend the database runs in Full Recovery Mode and a full database backup was taken since this setting was activated. In such case, with log backups accessible to the HVR executable in a native SQL Server format, we can safely move the truncation point forward. The HVR Solution will transparently read from the backups as needed. By default the capture job will call sp_repldone to move the truncation point forward on every capture cycle (which is every couple of seconds on a busy database with HVR running continuously).

Deployment of the HVR Solution is very flexible. For example, you may configure HVR's change data capture on a standby node of an AlwaysOn cluster. In such case you still must move the truncation point on the primary database. This can be done by simply calling sp_repldone at an interval e.g. every few minutes or every time you take a backup. More information, as well as the exact command to mark all transactions in the log as replicated, are in the SQL Server documentation.

Final comment about truncation points

When you introduce CDC tables or articles in your SQL Server database, either manually or through HVR activate, you implicitly activate the truncation point for replication. If/when you decide to stop using CDC on your database it is important these objects are cleaned up. HVR's activate has a deactivation option that does this for you. However, depending on the sequence of operations through HVR tables may not be deactivated properly, and you must do some manual cleanup. In all cases, use:
select log_reuse_wait_desc from sys.databases;
to find out why the TLog cannot be truncated if you expect it could be truncated.