How to Manage Truncation Points in SQL Server

Question

How can I manage truncation points in SQL Server?

Environment

• HVR 6
• Source: SQL Server

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 HVR 6 impact truncation points?

HVR 6 has its own logic to parse transaction information from the transaction log with a binary log reader, which has minimal impact on database processing. However, to perform change data capture in a heterogeneous environment, you would 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, this extra information is written to the log. Therefore, you must either have articles or CDC tables on top of the base tables to use HVR 6.

NOTE: Articles and CDC tables each introduce restrictions on DML operations, so choose which option is right for you. HVR 6 defaults to using CDC tables.

HVR 6 is designed to provide flexible change data capture/data replication for high-volume environments with the least possible impact on database processing. Since we wrote a binary log reader, we do not require the database to perform any change data capture processing. When articles are enabled, we do not 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 them to be populated, saving on database processing and storage requirements. With the default configuration, the HVR activate operation for SQL Server deletes the SQL Server Agent job that performs these tasks, reducing the overhead introduced to SQL Server.

IMPORTANT: HVR 6 can be configured to coexist with other data replication solutions - either native SQL Server replication or other third-party solutions. In such cases, we do not make any assumptions about the need for the SQL Server Agent jobs and leave them in place. Learn more in our Supplemental Logging documentation.

Without SQL Server Agent job in place to manage harvesting changes from the TLog and moving the truncation point for replication, something else must move this truncation point. We recommend running your database in Full Recovery mode and taking a full database backup since this setting was activated. With log backups accessible to the HVR executable in a native SQL Server format, we can safely move the truncation point forward. HVR 6 transparently reads from the backups as needed. By default, the capture job calls sp_repldone to move the truncation point forward on every capture cycle, which occurs every few seconds on a busy database with HVR running continuously.

Deployment of HVR 6 is flexible. For example, you may configure HVR's change data capture on a standby node of an AlwaysOn cluster. In this case, you must still move the truncation point on the primary database by calling sp_repldone at an interval (for example, every few minutes or every time you take a backup). See more details, including the exact command to mark all transactions in the log as replicated, in SQL Server's sp_repldone (Transact-SQL) documentation.

Cleanup after CDC change

When you introduce CDC tables or articles in your SQL Server database manually or through HVR activate, 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. HVR has a deactivation option that performs this cleanup for you. However, depending on the sequence of operations, HVR tables may not be deactivated properly, requiring 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 to be truncated.