InterBase has an (alleged) 2GB Database size limit

Firebird V1.0 and InterBase V6.5 onwards

This was an issue with InterBase Before V6.5 and previous, Where it was possible that if you didn't create an extended database file (by adding a new secondary file) then the 32-bit API I/O calls used by the database server would set the limit of a single database file at usually 2GB, when the limit was reached the database would corrupt itself by writing over the initial database pages. One of the first things we did in Firebird was change this so that if the limit was reached all writes would be prevented, although outstanding writes would be lost, this would make sure that even though the limit had been reached no corruption would occur. Then Firebird added Large Database File Support (see below). From Firebird V1.0 Large File Support was enabled automaticlly for Windows and then from Firebird V1.5, Large File Support was enabled automatically for other operating systems, meaning that you got 64bit I/O in all builds. i.e. prior to V1.5 you could get either 32bit I/O or 64bit I/O builds for Linux and other operating systems where appropriate.

Taken from the Firebird V1.0 Release Notes

Firebird Before V1.0 and InterBase Before V6.5

Generically speaking (i.e. from a platform-independent point of view), InterBase documents a 2GB/file limit. As a consequence of using the overlapped I/O mechanism on Windows NT, the file offset is interpreted as an unsigned 32-bit quantity from the beginning of the file. This allows you to access 4GB file even though we didn't plan it this way. However, don't try anything beyond 4GB as InterBase zeroes the file offset high <64:32> bits.

In fact, InterBase will probably be happy to logically allocate pages beyond that 4GB limit but the resulting physical I/O will be modulo 4GB and likely canabalize real data in the database.

For Windows95, InterBase still uses SetFilePointer which allows only a 2GB file limit due to bidirectional seeks from the beginning, current and end of a file. For the brave souls that file copy between Windows NT and 95, keep in mind that any database file greater than 2GB will not be accessible on Windows95 or more precisely only the first 2GB will be accessible.

So, InterBase uses 32-bit file pointers internally, and many operating systems typically implement file pointers as signed data, which limits them to 2^31-1, or about 2GB. Thus InterBase supports only database files of 2GB or less. NT does allow larger files to exist.

But we do recommend breaking up the database into multiple files of 2GB's each so you do not not to risk surfacing idiosyncracies related to the sign bit of the file pointer.

If your database is growing such that the 2GB file size limit is within sight, you would be prudent to define secondary file(s) even if you aren't so close to reaching the limit. InterBase will automatically start using them when it becomes necessary. You can add the additional files to a fileset of a given database on the fly, without interrupting activity on the database.

Defining secondary files is easy to do; refer to the docs, or the examples below.

Adding database files: You have the option of specifying the size of secondary files in either of two ways: specify the page on which each secondary file starts, or specify the length in database pages of each file. When you specify the size using the LENGTH keyword, do not specify the length of the final file. InterBase sizes the final file dynamically, as needed. The following isql example adds files using STARTING AT syntax:

CONNECT ‘first.gdb’; ALTER DATABASE
ADD FILE 'second.gdb' STARTING AT 50000;

Altering database file sizes You cannot use ALTER DATABASE to split an existing database file. For example, if your existing database is 80,000 pages long and you issue the command above, InterBase starts the new database file at page 80,001. The only way to split an existing database file into smaller files is to back it up and restore it. When you restore a database, you are free to specify secondary file sizes at will, without reference to the original file sizes. The following isql example adds a file using LENGTH syntax. Second.gdb will begin on the page following the final page of first.gdb and will grow to 50,000 database pages. Then InterBase begins writing to third.gdb and dynamically increases the size as necessary.

CONNECT ’first.gdb’; ALTER DATABASE
ADD FILE ’second.gdb’ LENGTH 50000 ADD FILE ’third.gdb’;

InterBase starts writing data to third.gdb once second.gdb file fills up. In the example above, second.gdb is 50,000 pages long, and begins following the original file. InterBase will begin filling the third.gdb file after second.gdb reaches 50,000 pages. Database pages are 1KB each by default and have a maximum size of 8KB. There is no guarantee that a given table resides entirely in one file or another. InterBase stores records based on available space within database files. Over time, records from a given table tend to spread over all the files in a multifile database.

Backing up a multi-file database that exceeds the file size limitation, can be done using GBAK, and it will prompt you automatically to insert additional tape volumes.

Alternatively if you are using InterBase 5.x on Unix, you can use the gsplit utility to pipe input and output from gbak to multiple backup volumes. If you are using InterBase 5.x on Windows or NT download the fsplit utility (Miscellaneous) from this site which serves the same purpose.

If you are using InterBase 6.0, the syntax would be something like this example, where foo1.gdb on a server is backed up into multiple files in the /backup directory, where the first two backup files are limited to 500K. The last one expands as necessary.

gbak -b -user joe -pa blurf /foo1.gdb
/backup/backup1.gbk 500k /backup/backup2.gbk 500k /backup/lastBackup.gbk

This example restores a multifile database from the /backup directory of to the /companydb directory. In this example, the first two files of the restored database are 500 pages long and the last file grows as needed.

gbak -r user -joe -pa blurf /backup/foo1.gbk
/backup/foo2.gbk /backup/fooLast.gbk /companydb/foo1.gdb 500
/companydb/foo2.gdb 500 /companydb/fooLast.gdb

Regarding the theoretical maximum database size:

InterBase internally enumerates the multiple files with a 16-bit unsigned integers, therefore you may have up to 65,536 files. Assuming you choose file of the maximum supported size, you can theoretically address up to 2^(31+16)-1 bytes, or 131,072 terrabytes of data.