Estimate the size and number of log files

Use the LOGSIZE configuration parameter to set the size of the logical-log files.

The amount of log space that is optimal for your database server system depends on the following factors:
  • Your application requirements and the amount of update activity your applications experience. Increased update activity requires increased log space.
  • The recovery time objective (RTO) standards for the amount of time, in seconds, that the server is given to recover from a problem after you restart the server and bring it into online or quiescent mode.

    In the case of a catastrophic event, consider how much data loss you can tolerate. More frequent log backups, which reduce the risk of data and transaction loss, require increased log space.

  • Whether you use Enterprise Replication or data replication configurations such as HDR secondary, SD secondary or RS secondary servers.

    These replication services can influence the number and size of log files. If your system uses any of these replication services, see guidelines in High-availability cluster configuration or in the HCL OneDB™ Enterprise Replication Guide.

Some guidelines for determining log size are:
  • Generally, you can more easily manage a few large log files than you can manage many small log files.
  • Having too much log space does not affect performance. However, not having enough log files and log space can affect performance, because the database server triggers frequent checkpoints.
  • Smart large objects in blobspaces are not logged, but they are included in the log backup in which the object was created. This means that the objects are not freed until the server backs up the log in which they were created. Therefore, if smart large objects in a blobspace are frequently updated, you might require more frequent log backups to acquire additional free space within a blobspace.
  • For applications that generate a small amount of log data, start with 10 log files of 10 megabytes each.
  • For applications that generate a large amount of log data, start with 10 log files with 100 megabytes.
There are two ways to maintain an RTO policy, which determines the tolerance for loss of data in case of a catastrophic event such as the loss of the data server:
  • One way to maintain an RTO policy is to use automatic log backups that trigger log backups whenever a log file fills up. This limits data loss to the transactions contained in the log file during the backup, plus any additional transactions that occur during the log backup.
  • Another way to maintain an RTO policy is to use the Scheduler. You can create a task that automatically backs up any new log data at timed intervals since the last log backup. This limits data loss to the transactions not backed up between time intervals. For information about using the Scheduler, see The Scheduler.

If an RTO policy is required, you can use the Scheduler to insert a task that executes at an appropriate frequency to maintain the policy. This automatically backs up log files at certain times within the daily cycle. If the log space fills before the logs being backed up and recycled, you can back up the logs and add a new log file to allow transaction processing to continue, or you can use the Scheduler to add a new task to detect this situation and perform either operation automatically.

You can add log files at any time, and the database server automatically adds log files when required for transaction consistency, for example, for long transactions that might consume large amounts of log space.

The easiest way to increase the amount of space for the logical log is to add another logical-log file. See Adding logical-log files manually.

The following expression provides an example total-log-space configuration, in KB:
LOGSIZE = (((connections * maxrows) * rowsize) / 1024) / LOGFILES
Expression element Explanation
LOGSIZE Specifies the size of each logical-log file in KB.
connections Specifies the maximum number of connections for all network types that you specify in the sqlhosts file or registry and in the NETTYPE parameter. If you configured more than one connection by setting multiple NETTYPE configuration parameters in your configuration file, add the users fields for each NETTYPE, and substitute this total for connections in the preceding formula.
maxrows Specifies the largest number of rows to be updated in a single transaction.
rowsize Specifies the average size of a table row in bytes. To calculate the rowsize, add the length (from the syscolumns system catalog table) of the columns in the row.
1024 Converts the LOGSIZE to the specified units of KB.
LOGFILES Specifies the number of logical-log files.