Managing Relativity SQL log files

This page provides tips for keeping log files healthy and a procedure for properly sizing log files. Keep in mind that you should consistently monitor the log files because, if the log drive fills up, and SQL stops, Relativity will also stop working.

This page contains the following information:

Overview

Relativity databases typically exist in fast-growing environments with high activity.Therefore, it’s important for the transaction logs to be healthy because they keep a history of all executed actions. Unhealthy log files can result in bottlenecks even if they are small in size. Tiny log files that experience large transactions can slow down an environment because, even with instant file initialization turned on, they still need to write zeroes in order to grow.

Log maintenance tips

Consider the following tips when maintaining log files:

  • Ensure that log file backups are set to run at least once every hour. Consider running them more often in the following scenarios:
    • You want to recover with less than one hour of data loss.
    • Your system writes more data to the log file in one hour than is ideal.
    • The amount of data written to the log file in one hour causes the log file to fill the drive. SQL marks space in the log file as reusable once it is backed up, so it shouldn’t grow too large.

      Note: Some single transactions in Relativity are quite large and may result in log file growth through multiple transaction log backups. This can prevent successful log backups. Contact Client Services if this happens, and let them know what action caused it.

  • Use the Dr. FeelGood utility, which can be found in the Customer Portal, to check how many Virtual Log Files (VLFs) appear in the logs. We recommend having 50 for most databases, although more than 50 VLFs are acceptable for large databases. Having more than 1,000 VLFs can indicate a problem that requires attention.
  • Ensure the autogrowth of the log files is set to at least 512 MB, but no more than 1 GB. When log file growth occurs, zeroes must be written to disk before anything else is written to the log file. Setting autogrowth too high can cause other transactional activity to be suspended while autogrowth is in progress.
  • In certain situations, when Relativity begins an operation, such as a large data load, many gigabytes of data can pour into the log file before the next scheduled log backup marks space as available. Taking more frequent log backups can mitigate this kind of issue. Unanticipated growth can also occur when extremely large transactions run. Should this occur, contact support@relativity.com.

If you find that, even with proper transaction log backups, your log files are growing too large, contact Client Services. If you know that you are creating hundreds of gigabytes of new data in your database, we strongly recommend that you grow the log file during a maintenance window beforehand. This reduces environmental impact when loading during the day.

Setting the size of a SQL log file

The following procedure gets you to about 50 VLFs using the Set Initial Size interface. Log file growth uses the following logic to decide how many VLFs to make:

SQL Server

VLFs
SQL Server 2012
  • For growth of 64 MB or less, 4 new VLFs are added.
  • For growth between 64 MB and 1 GB, 8 new VLFs are added.
  • For growth over 1 GB, 16 new VLFs are added.
SQL Server 2014
  • For growth of one eighth of the current log file size or more, the same formula for SQL Server 2012 is used.
  • For growth of less than one eighth of the current log file size.
SQL Server 2016
  • For growth of one eighth of the current log file size or more, the same formula for SQL Server 2012 is used.
  • For growth of less than one eighth of the current log file size.

Before you begin this procedure, perform a full backup, and make sure there are no transactions running.

Note: This work may have heavy environmental impact. You should only perform this process in a maintenance window.

Perform the following steps to set the size of a SQL log file:

Note: You should test this process on a test database, and time it as a baseline.

  1. Right-click the database, and then select Properties.
  2. Click Files.
  3. Click Initial Size, and then set it to 1. This decreases your start size to 3 VLFs. If the log file does not shrink, run Truncate Logs v3.5. If it does shrink, run a script to truncate the log.
  4. Click Initial Size, and then set it to 1/3 the size you want. This can take a few seconds or several minutes as the log file grows.

    For a target file size greater than or equal to 25 GB, grow the file in 8 GB chunks rather than thirds of the target size.

  5. Set the Initial Size value to 2/3 the final size.

    For a target file size greater than or equal to 25 GB, grow the file in 8 GB chunks repeatedly until you're within 8 GB of the target size.

  6. Set the Initial Size value to the target size.

This method should produce around 50 VLFs or a value twice that of your target file size in GB for larger databases.

Run the Dr. FeelGood utility to see your results. New workspaces copy the LDF file from the database of the template case, but the LDF for the new workspace shrinks when the systems creates the workspace. This means that new workspaces always have approximately the same LDF file size and VLF count when they're created and should have this maintenance procedure performed prior to any large data imports.

Log file sizing considerations

If you know you’re going to perform maintenance on your database that requires a complete copy of a large table, consider making the log file as big as that table and its indexes. You should continuously monitor your log files and adjust accordingly.