Show TOC

Procedure documentationDatabase Transaction Logs

Procedure

All changes to the database are stored in the transaction log in the order that they occur. Inserts, update, deletes, commits, rollbacks and database schema changes are all logged. The transaction log is called a forward log file.

As the database is modified the transaction log grows until it is truncated, either by a database backup or during a checkpoint if the automatic option is set.

The transaction log records data modification requests (update, insert or delete statements) before they are executed. When a transaction begins a begin transaction event is recorded in the log. This event is used during automatic recovery to determine the starting point of the transaction.

As each data modification statement is received it is recorded in the log. The change is always recorded in the log before that change is made in the database itself. At the end of the transaction a commit transaction record is logged. This delineates the transaction and also allows automatic recovery to query this log record and find out if the transaction completed successfully.

When a backup of the database is done the transaction log can be affected in different ways. When requesting a database backup using the DBTools application you have 3 choices as to what will happen the database log. The three choices are as follows:

  • Rename and Start a New Transaction Log: This option forces a checkpoint and the following to occur:

    • A copy is made of the current working transaction log file and saved to the directory specified in the command line.

    • The current transaction log remains in its current directory but is renamed using the format YYMMDDxx.log where xx is a number from 00 to 99 and YYMMDD represents the current year, month and day. This file is then no longer the current transaction log.

    • A new transaction log file is generated that contains no transactions. It is given the name of the file previously considered the current transaction log and is used by the database engine as the current transaction log.

  • Delete and Restart the Transaction Log: With this option the existing transaction log is backup up then the original is deleted and a new transaction log is started with the same name. This option causes the backup to wait for a point when all transactions from all connections are committed.

  • Backup the Transaction Log Only: This can be used as an incremental backup since the transaction log can be applied to the most recently backed up copy of the database files.