Firebird and Temp files

I recently upgraded a medium size database (40 Gigabytes) and during the upgrade the system experienced instability which at first I could not understand - after troubleshooting it I came to a better understanding of Temporary Files and how it can cause disk space issues which could result in system instability.

When does Firebird make use of temp 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 - for the most part this will not happen during normal Case Manager operations seeing that the system has been optimized to use indexes on all queries ,however, there are cases where un-optimized queries could be executed for example:

• Custom reports which generate very large result sets across multiple tables.
• Upgrade procedures where large database changes are performed.
• Custom scripts which perform custom selects across multiple tables. 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.

Explanation of how temp files caused system instability

In my specific case I came across the issue during an upgrade - a temp file of about 25 Gigabytes was created during a pretty intensive upgrade statement (specifically statement 5.1.4.0 - 133). The system kept falling over during this statement and I was a bit shell shocked trying to understand why I am not receiving a proper error - looking back it now makes perfect sense

The system configuration was as follows:

The C: drive hosted both the operating system and the database - this is not ideal but certainly not uncommon. The available space on the C drive was 25 Gigabytes - during the upgrade statement in question a massive Temp File was created which slowly used up all the available space on the C drive which in turn resulted in Operating System instability. Once the system became unstable the upgrade procedure terminated without giving a clear error - the temp file was actually left-over and I had to manually delete it.

Best practices for temp files

I have to be honest that I rarely think about temp files - going forward I will be more cognizant of these little (or not so little) guys.

• It is always better to have your temp files on a disk separate from the main database - this will increase performance.
• Assume that your temp files can become very large (around the size of your database) and leave enough space free for them to grow to this size.
• Explore the impact of the TempCacheLimit setting in Firebird on the use of Temp Files - I intent to do some research on this and will report back once I understand this better. Some final thoughts I mentioned that I never really think about temp files. The reason for this is that in my twenty years of dealing with hundreds of Firebird deployments I have almost never had any issues with them ,however, now that I have encountered issues I realize that you have to be aware of some best practices. I know that some of my colleagues have been experiencing issues during upgrades - it might very well be related to temp files. Cheers!