AUTO_LLOG and its effect on logging

Insufficient logical logs can affect performance by triggering frequent checkpoints, blocking checkpoints, or long checkpoints. The AUTO_LLOG configuration parameter controls whether the database server automatically adds logical logs to improve performance.

If you created a server during installation, the AUTO_LLOG configuration parameter is enabled automatically. Otherwise, you can edit the value of the AUTO_LLOG configuration parameter.

If the AUTO_LLOG configuration parameter is enabled, the database server automatically adds logical log files under the following circumstances:

• When a substantial portion of the last 20 checkpoints were caused by logical logs filling up
• When inadequate logical log space causes a blocking checkpoint
• When inadequate logical log space causes a long checkpoint

The AUTO_LLOG configuration parameter also specifies the dbspace for new logical log files and the maximum size of all logical log files before the server stops adding logical logs for performance. The following guidelines show estimates of the maximum amount of space for logical logs that you might need, depending on the number of concurrent users who access your database server:

• 1 - 100 users: 200 MB
• 101 - 500 users: 5 MB
• 501 - 1000 users: 1 GB
• More than 1000 users: 2 GB

The settings of the AUTO_LLOG configuration parameter and the DYNAMIC_LOGS configuration parameters do not interact.