Pervasive (formerly and informally "Btrieve") data files can become extremely large with little to no loss in performance as file size increases making it a very attractive database record manager solution for database oriented applications such as accounting software and is the native database used by Advanced Accounting and TAS Premier.

Starting with Pervasive v9 (version 9), the maximum data file size can now be as large as 128 gigabytes (for v9.5 and v10, the maximum is 256 gigs). The word “data file” may be in the context of other database systems be thought of as a “table” however with Pervasive/Btrieve data files, each “table” is stored as a separate physical data file on the drive. With Pervasive v8 as well as prior format 7 which includes the 2000 and 2000i versions of Pervasive, the maximum data file size is 64 gigs. The maximum sizes for Btrieve 6.15 and Btrieve 5.10a is 4 gigs.

The maximum size is also dependent, in addition to file version, on the page size and the number of records per page. For more information see http://www.pervasive.com//library/docs/psql/910/advops/advops-02-2.html. With Btrieve 5.10a, the practical limit seems to be 2 gigs and most Advanced Accounting 5.1 users never even come close to reaching that limit on any data file, we have worked with older custom systems where that size was reached with Btrieve 5.10a and the application did not work correctly until the data was archived (or another solution would have been to move the user to the latest version of Pervasive).

When a Pervasive/Btrieve file reaches 2 gigs and regardless of what version of Pervasive is in use (thru the latest version which as of the date of the original date of this tech support memo was v9) Pervasive automatically splits or segments the file and continues to do so each time a segment reaches this maximum size. There is nothing that a programmer has to worry about and the segmentation is transparent to the end user and programmer alike: the segmentation occurs automatically and as far as the programmer or end user is concerned, there is still just one “table” even though the physical data file is split potentially into multiple pieces.

New to Pervasive v9 is the option to tell Pervasive to NOT segment a file should it reach the 2 gig size. This is not the default setting. Our recommendation to V9 and above users was to change that default option when this tech support memo was first published, since it should provide better performance, would be simpler to administer for back-up/data replication purposes assuming there are no 2 gig limitations of any kind that exist elsewhere, and it likely makes it easier to access the file via ODBC in the albeit unlikely event that file size is reached. The property “Limit Segment Size to 2GB” in the Pervasive.SQL 9 Control Center should therefore be selected off. (See screen shot at the end of this memo which shows the Performance tuning --> Limit segment size to 2gb setting.)

Note that after making this setting change, you will need to restart the Pervasive engine/service (users will have to all be out of software). Also very important is that you must make sure that the file's physical format is not prior to V8. Any file prior to V8 format must be rebuilt. If a file already has segmented, you will need to make the setting change, restart the engine, and then rebuild the file using the Pervasive REBUILD utility into at least version 9.0 and this will remove the segmentation (a file previously split, for example, BKICTRAN.B and BKICTRAN.^B will then be re-integrated). Users should not attempt to use the data file being rebuilt or else data loss will occur, and large files with many indices typically will take a long time to rebuild.

Note that the foregoing assumes that the drive contains an NTFS partition. Older operating systems typically will not be able to support a file larger than 2 gigs.