Temporary files

Firebird temp files are created when something needs to be sorted or combined from multiple tables and no index is usable or there is not enough sort memory available.

When the Firebird server receives a query including ORDER BY or similar, without an index, then Firebird has to sort the data somewhere. Firebird has a so-called Sort Buffer, which is principally a memory area where such sorting processes can be performed. If however you have a sorting operation that is 10 GB, Firebird needs somewhere to do this. From a certain size, when the Sort Buffer is no longer sufficient, it moves the job out into a temporary file or files, and you can specify here where these temp files should be.

The default value is determined using firebird_tmp, temp or tmp environment options. Every directory item may have optional size argument to limit its storage; this argument follows the directory name and must be separated by at least one space character. If the size argument is omitted or invalid, then all available space in this directory will be used.

Firebird temp files begin with FB and, by default, they are stored in the Windows /temp directory, when the Firebird server is installed as a service. The Firebird temp directory can be altered and specified in the firebird.conf by defining the parameter, TempDirectories. Firebird then uses those directories specified here, and – only when there is insufficient space – goes on to use the Windows directories.

TempDirectories = c:\temp;d:\temp

or

TempDirectories = c:\temp 100000000;d:\temp 500000000;e:\temp

Temp files can get very big very quickly. In fact, they can get much bigger than the current database size. It’s worth observing them to see just how they grow when you have certain large batch processes running, for example when you have a huge join on your nominal ledger. It’s even possible to ascertain from the sort files roughly how far you are in the process, once you’ve gained a little experience monitoring them.

One of the reasons for this is that temp files also include the full space definition for long CHAR or VARCHAR columns.

So be careful when defining your character fields. Many developers are very generous when defining chars and varchars because they know that Firebird stores them in binary form with the string content plus the number of empty spaces (and not the empty spaces themselves).

However, when these fields are loaded into the temp directory, they are written out in full. For this reason it often makes more sense to define a text blob rather than a huge varchar. Blob fields are only limited in size by the page size; for example, with a page size of 8 KB the maximum blob size is 32 Gigabytes.

It can also sometimes be advantageous to compress the temp files. Firebird temp files begin with FB and, by default, they are stored in the Windows/temp directory, when the Firebird server is installed as a service. The Firebird temp directory or directories can be altered and specified in the firebird.conf by defining the parameter, TempDirectories.