Blackfish SQL transaction log files

By: Steven Shaughnessy

Abstract: Overview of the purpose, configuration and truncation of Blackfish SQL transactional log files

Blackfish SQL transaction log files are used to support transaction rollback and crash recovery. Log files have a maximum size which currently defaults to 64 megabytes. When the maximum size is reached, a new log file is created. When no active transaction needs the log records in an older log file, the system will automatically drop the older log file.

    Configuration options

There are several configuration options that can be specified through one of the database console utilities or through the use of the built in DB_ADMIN.ALTER_DATABASE stored procedure. The DataExplorer included in RAD Studio has a “Database|Modify Database Properties” shortcut menu off of the connection node for changing the transaction log configuration. The JdsExplorer included in Blackfish SQL for Java has “TxManager|Modify..” menu for changing transaction log configuration.

There are several configuration options including block size, maximum log file size, and checkpoint frequency.

    Truncating log files

There are two possible approaches to truncating log files. The first is to copy the database to a new database. This has the added benefit of compacting the storage of the database itself. However, this operation does require that the entire contents of the database be copied into another database. The second option is to disable the transaction log files and then reenable them.

The RAD Studio DataExplorer has a “Database|Copy Database” shortcut menu off of the connection node that can be used to copy the database. The JdsExplorer included in Blackfish SQL for Java has a Tools|Copy database…” menu. You can also use DB_ADMIN stored procedures described in the Database backup for Blackfish SQL article to make a copy of the database.

To truncate the log files without making a copy of the database, you can use the DB_ADMIN.ALTER_DATABASE built in stored procedure to disable and then reenable the log files.




Server Response from: ETNASC03