Creating a Multi-File Database - Firebird

Multi-file databases are more of an issue on older filesystems where the absolute limit for a shared-write file is 2GB (FAT32, ext2) or 4GB (NTFS systems with 32-bit I/O). It used to be a common support problem for users to corrupt InterBase databases by “blowing the limit” and the server would start overwriting the file from the beginning. A similar problem occurred if a database exhausted its secondary file capacity. Firebird simply denies all writes when the last file hits the limit. Corruption of existing data is thus prevented, although any outstanding writes will be lost.

In the following example, a database is created consisting of three files, each potentially 2GB. If the filesystem supports a larger shared-access file, the last file will continue to grow until the filesystem limit (if any) is reached.

You must specify a range of pages for each file either by providing the number of pages in each file or by providing the starting page number for the file. For the last file, you do not need a length, because Firebird always dynamically sizes the last file and will increase the file size as necessary until all the available space is used or until it reaches the filesystem limit.

In the example, the first secondary file will “kick in” when the first primary file is nearing the 2GB limit. The “next file in the chain” comes into use when a requested operation is likely to need more pages allocated than the previous files could satisfy without exceeding their specified limits.

It is the responsibility of the database administrator to monitor database growth and ensure that a database always has ample capacity for extension. Deciding if and when to split a database file depends on how large you expect the database to grow and how quickly. More files can be added at any time using the ALTER DATABASE statement (see the next section).

With multi-file databases, you can avoid confining databases to the size of a single disk if the system does not support spanning a single, huge file across multiple disks. There will be no problems installing a RAID array and distributing a multi-file Firebird database across several disks on any supported platform.

Altering the Database

The ALTER DATABASE statement is used to add one or more secondary files to an existing database. It requires exclusive access to the database.

A database can be altered by its creator (owner), the SYSDBA user or, on Linux/ UNIX, any user with operating system root privileges.


The syntax for ALTER DATABASE is

The first example adds two secondary files to the currently connected database by specifying the starting page numbers:

The first secondary file will grow until it nears 10,000 pages. When it, too, is determined to have insufficient capacity left to satisfy new page requests, Firebird will begin storing new pages in the second secondary file.

The next example specifies the secondary file length rather than the starting page number:

The effect is slightly different from the first example. In this case, Firebird will begin using the second file when the primary file reaches the point where one more page would be larger than the filesystem limit.

The difference has no effect on performance or overall database size.

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

Firebird Topics