While working on an issue involving memo block size I found out that this is not always an easy value to determine. The default memo block size for Advantage memo file (ADM) is 8 bytes. DBFs have two styles of memos FPT and DBF. The default memo block sizes for FPT files is 64 bytes and DBT files is 512 bytes.
There are two major implications of this memo block size. First the blocks determine how efficiently the data is stored. Every time there is more data stored in the memo than is available in the currently allocated blocks more blocks are added. Therefore a 65 character string in a memo field will take up the following space:
- ADM – 9 Blocks 72 bytes
- FPT – 2 Blocks 128 bytes
- DBT – 1 Block 512 bytes
The default block size for ADTs make for a very efficient storage of data in the memo file. Although more blocks are required space is preserved since the blocks are smaller.
The second implication is the amount of data that can be stored in the memo file. There can only be 4,294,967,296 (4GB) of memo blocks in any memo file. Thus you can calculate the maximum size of the memo file by multiplying the block size times 4GB. For example maximum file size based on the defaults are:
- ADM – 8 bytes X 4 GB = 32 GB
- FPT – 64 bytes X 4 GB = 254 GB
- DBT – 512 bytes X 4 GB = 2 TB
When you have a need to store large amounts of data in a memo file you may need to increase the memo block size. When the maximum number of memo blocks have been allocated a 7073 error will be returned. If you receive this error you can either pack the table to free up some of the memo blocks or create a new table with a larger memo block size. Valid memo block sizes are from 8 – 1024 bytes. This allows for a memo file size between 32 GB and 4 TB.
You can specify the memo block size when creating a table in ARC. The AdsCreateTable API includes a parameter to specify the memo block size. This option is also available when using the TAdsTable component for Delphi.
You can determine the current memo block size by using the AdsGetMemoBlockSize API function. This value is also available on dictionary bound tables within the system.tables system table. Only tables with associated memo files will report a value in the Table_Memo_Block_Size column.
In most cases the default memo block size will be sufficient. However, if you expect to store a large amount of data (i.e. images, documents, etc…) in a table you may want to increase the memo block size when you create the table. Keep in mind that too large a block size can lead to inefficient storage.