Managing SQL Server Log File Truncation

Last updated on May 11, 2020

SQL Server

Contents

Overview

SQL Server log file need to be truncated periodically to prevent its excessive growth. HVR provides following options for that:

  • have HVR capture job with automatic log truncation turned on. It suits most simple cases, easily configurable and provides good performance;
  • employ Hvrlogrelease command. This may be required for more complex scenarios and requires more configuration;
  • utilize native SQL Server agent to read and truncate log file.

Capture Job Log Truncation

This is default option for HVR capture job. Here log file truncation is performed automatically by HVR capture job when it runs. It's fast and powerful and yet simple to use as it has zero configuration. Yet it has disadvantage to not fit in following scenarios:

  • coexistence, when several brands of replication tools capture simultaneously from the same database;
  • multi-capture, when several HVR capture jobs capture simultaneously from the same database;
  • long period of HVR capture job inactivity had already happened or planned, so that regular automatic log file truncation was or is impossible.
    In such cases it's better to apply Hvrlogrelease command as described below.

Native SQL Server Agent

HVR capture can work along with native SQL Server agent. But this option should be taken with care as it's sensitive to configuration errors. So it's suggested to consult with HVR support team to decide whether it needed and how HVR capture job need to be configured.

Hvrlogrelease

Hvrlogrelease command allows to deal with complex scenarios where HVR capture job is not enough such as multi-capture and long HVR capture job inactivity. This approach employs having specific HVR task to run Hvrlogrelease command to truncate log file. Such task can be run either manually or by schedule once configured.

Hvrlogrelease Configuration

Hvrlogrelease task can be configured from HVRGUI or in the command line.

  1. Right-click on SQL Server location and select Log Release.


    Log Release menu item is enabled only for SQL Server location class.

  2. Hvrlogrelease task options can be configured in the top part of the dialog box.
  3. For HVR hub on Windows, a user with chosen privileges level can schedule the task to run at specific time intervals (by configuring the Time options). This option is not available in Linux.
  4. Click Save to store the configuration to optfile.
  5. It's also possible to run the task immediately by clicking the Run button.