Managing Db2 transaction logs

When your instance is running many transactions, your archive log directory can fill and you might have to remove log files to free up space.

About this task

You can either use the PRUNE HISTORY command or manually delete unneeded log files and directories. It is important to choose a method that you are confident prevents the accidental loss of essential log files.

If the Db2 instance is failing because of a lack of space, prior to pruning logs you can edit the associated PVC and increase the size that is allocated. The storage class that you are using must have allowVolumeExpansion set to True.

Note: Before you remove log directories, make sure that no backup or restore operation is running. You can use the db2 list utilities command to confirm.

Procedure

Use one of these methods to manage log files:

Use the Db2 PRUNE HISTORY command

Find the timestamp of the logs that you want to prune (up to and including) by running the following command:

db2 list history all for database-name | egrep "B P|B D|X D"

The command returns the timestamps of the following storage items:

X D:log archive
B P:tablespace backup
B D:full backup

The basic prune history command, PRUNE HISTORY timestamp, allows Db2 to maintain a restore set.

If you use the PRUNE HISTORY timestamp WITH FORCE OPTION command, Db2 does not keep the restore set.

You can also use the

PRUNE HISTORY timestamp AND
DELETE

command to delete the unneeded archive log files that are related to the pruned history.

db2 prune history 20221021050641 and delete

For more details, see the following topics:

Manually delete unneeded log directories and files

By default, Db2 archives transaction logs to disk with the following settings:

First log archive method (LOGARCHMETH1): DISK:/mnt/bludata0/db2/archive_log/
Number of primary log files (LOGPRIMARY): 20
Number of secondary log files (LOGSECOND): 30

The following formula is used to determine how many logs are kept in each multiple logical node (MLN):

(log primary + log secondary) * 2

By default, (20 + 30) * 2 or 100 logs are kept.

To maintain this number of logs, assume that you have a deployment of one MLN. Archived logs are kept in sub-directories whose name starts with "C" in the /mnt/bludata0/db2/archive_log/db2inst1/BLUDB/NODE0000/LOGSTREAM0000 directory.

If directories from C0000000 to C0000005 exist, you would remove C0000000 to C0000004.

Then, within the C0000005 directory, you would keep only the latest 100 log files. The file names begin with "S." If the C0000005 directory has S0000000.LOG through S0000200.LOG, you would keep S0000101.LOG through S0000200.LOG.

You then repeat this process for each MLN, NODE0000 to NODExxxx.

To remove the log directories, use the following command while logged in as the db2inst1 user:

sudo rm -r directory