Database “Hygiene” - Firebird

Firebird uses a multi-generational architecture. This means that multiple versions of data rows are stored directly on the data pages. When a row is updated or deleted, Firebird keeps a copy of the old state of the record and creates a new version. This proliferation of record back versions can increase the size of a database.

Background Garbage Collection

To limit this growth, Firebird continually performs garbage collection in the background of normal database activity.

The background garbage collection does nothing to obsolete row versions that are caught up in unresolved transactions—they will not be visited during normal housekeeping activity. To completely sanitize the database, Firebird can perform databasesweeping.


Database sweeping is a systematic way of removing all outdated row versions and freeing the pages they occupied so that they can be reused. Periodic sweeping prevents a database from growing unnecessarily large. Not surprisingly, although sweeping occurs in an asynchronous background thread, it can impose some cost on system performance.

By default, a Firebird database performs a sweep when the sweep interval reaches 20,000 transactions. Sweeping behavior is configurable, however: It can be left to run automatically, the sweep interval can be altered, or automatic sweeping can be disabled, to be run manually on demand instead.

Manual sweeping can be initiated from the command-line housekeeping program, gfix. Several other desktop tools are available that provide a GUI interface for initiating manual sweeps.

Sweep Interval

The Firebird server maintains an inventory of transactions. Any transaction that is uncommitted is known as an interesting transaction. The oldest of these “interesting” transactions (the Oldest Interesting Transaction, or OIT ) marks the starting point for the sweep interval. If the sweep interval setting is greater than zero, Firebird initiates a full sweep of the database when the difference between the OIT and the newest transaction passes the threshold set by the sweep interval.

Garbage Collection During Backup

Sweeping a database is not the only way to perform systematic garbage collection. Backing up a database achieves the same result, because the Firebird server must read every record, an action that forces garbage collection throughout the database. As a result, regularly backing up a database can reduce the need to sweep and helps to maintain better application performance.

Validation and Repair

Firebird provides utilities for validating the logical structures in databases and identifying minor problems and, to a limited extent, repairing them. A variety of such errors may appear from time to time, particularly in environments where networks are unstable or noisy, or the power supply is subject to fluctuation. User behavior and application or database design deficiencies are also frequent causes of logical corruption.

Abnormal termination of client connections does not affect database integrity, since the Firebird server will eventually detect the lost connection. It preserves committed data changes and rolls back any left pending. Cleanup is more of a housekeeping issue, since data pages that were assigned for uncommitted changes are left as “orphans.” Validation will detect such pages and free them for reassignment.

The validation tools are capable of detecting and removing minor anomalies caused by operating system or hardware faults. Such faults usually cause database integrity problems, due to corrupt writes to or loss of data or index pages.

Data thus lost or damaged is not recoverable, but its artifacts must be removed to restore database integrity. If a database containing these compromised structures is backed up, the database will not be capable of being restored. It is important, therefore, to follow a controlled course of action to detect errors, eliminate them if possible, and get the database back into a stable state.

When to Validate and Why

Periodic validation should be part of the database admin’s regular housekeeping, to detect minor anomalies and recycle misallocated space. Additionally, it will be required when structural damage is indicated or suspected. The indicators include

  • A “corrupt database” or “consistency check” error.
  • A backup that ends abnormally.
  • Power failure or brownout without UPS protection or with suspected UPS failure.
  • Suspected or system-reported hard disk, network, or memory faults.
  • A database shadow taking over from a dead database after a disk crash.
  • A production database is about to be moved to another platform or storage system.
  • Suspected compromise of the network or database by malicious attack.

What to Do About a Corrupt Database

If you suspect you have a corrupt database, it is important to follow a proper sequence of recovery steps in order to avoid further corruption. The first, most important thing to do is ask or, if necessary, force all users to cancel their work and log out.

How to Corrupt a Firebird Database

Firebird is famously tolerant of trauma fatal to other DBMS systems. However, experience has shown up a few techniques that have proven useful if destruction of your database is among your objectives. The author wishes to share these database -killers with the reader.

Modify the System Tables

Firebird stores and maintains all of the metadata for its own and your user-defined objects in—a Firebird database! More precisely, it stores them in relations (tables) right in the database itself. The identifiers for the system tables, their columns, and several other types of system objects begin with the characters “RDB$”.

Because these are ordinary database objects, they can be queried and manipulated just like your user-defined objects. However, just because you can does not mean you should.

It cannot be recommended too strongly that you use DDL—not direct SQL operations on the system tables—whenever you need to alter or remove metadata. Defer the “hot fix” stuff until your skills in SQL and your knowledge of the Firebird engine become very advanced. A wrecked database is neither pretty to behold nor cheap to repair.

Disable Forced Writes on Windows with Firebird 1.0.x

Firebird is installed with forced writes (synchronous writes) enabled by default. Changed and new data is written to disk immediately upon posting.

It is possible to configure a database to use asynchronous data writes, whereby modified or new data is held in the memory cache for periodic flushing to disk by the operating system’s I/O subsystem. The common term for this configuration is forced writes off (or disabled). It is sometimes resorted to, in order to improve performance during large batch operations.

Win32 server platforms do not flush the Firebird server v.1.0.x write cache until the Firebird service is shut down. Apart from power interruptions, there is much that can go wrong on a Windows server. If it should hang, the I/O system goes out of reach and your users’ work will be lost in the process of rebooting.

The big warning here is this: Do not disable forced writes on a Windows server unless you are using Firebird 1.5 or higher.

Firebird 1.5, by default, flushes the write cache every 5 seconds or at every 100 writes, whichever comes sooner. The frequency can be modified in firebird.conf by altering one or both of Max Unflushed Writes and Max Unflushed WriteTime.

Windows 95 does not support asynchronous writes to disk.

Linux servers are safer for running an operation with forced writes disabled temporarily. Do not leave it disabled once your large batch task is completed, unless you have a very robust fallback power system.

Restore a Backup to a Running Database

One of the restore options in the gbak utility (gbak -r[estore]) allows you to restore A gbak file over the top of an existing database—it overwrites it. It is possible for this style of restore to proceed without warning while users are logged into the database. Database corruption is almost certain to be the result.

Your admin tools and procedures must be designed to prevent any user (including SYSDBA) from overwriting your active database if any users are logged in.

If is practicable to do so, it is recommended to restore to spare disk space using the gbak -c[reate] option. Before making the restored database live, test it in the spare location using isql or your preferred admin tool.

Allow Users to Log In During a Restore

If your organization likes living on the edge, then use the –restore switch and let users log in and perform updates. Restore re-creates the database from scratch and, as soon as the tables are re-created, your users can, potentially at least, hit them with DML operations while referential integrity and other constraints are still in the pipeline. At best, they will cause exceptions and a stack of uncommitted transactions in the partly constructed database. At worst, they will thoroughly break data integrity in diverse, irrecoverable ways.

Copy Database Files While Users Are Logged In

Use any filesystem copying or archiving utility (DOS copy, xcopy, tar, gzip, WinZip, WinRAR, etc.) to copy files while any user (including SYSDBA) is logged in. The copy will be damaged but, worse, sector locking and/or caching by these programs can cause data loss and possibly corruption within the original file.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Firebird Topics