3

This one is interesting to me - despite the almost inane title. I have used Firebird for a long time, but not until recently noticed an interesting behavior.

I am using embedded Firebird 1.5, and noticed that if I stuff the database full of blobs (lets say 10mb worth), the size of the database increases. I can then delete all the fields in the database, and the file size of the DB remains at its expanded size. Currently it is at 20mb and is completely empty.

I know that Firebird has this built into its architecture (for quick indexing, speed issues etc), but I always thought it would decrease back down to its original ~2mb default.

Does anyone have any suggestions to 'deflate' the file size? The reason being is that this is a space conscious issue. If I had tons of space to work with, I wouldn't care. However that is not the case, and I need things to be as optimal as possible

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Liam
  • 83
  • 6

1 Answers1

5

The only way to free unused space in a firebird database is to do a backup then an immediate restore of that backup (Reference: Firebird FAQ).

Here is a good technical explanation of why this is so.

Note that Firebird will reuse the currently unused space - ie. if you put another 10MB of blobs in now, the database should not grow to 30MB.

Blorgbeard
  • 101,031
  • 48
  • 228
  • 272
  • thanks for the answer - but what if the size of the database is due to use. Such as storing values in it, or blobs. A user uses the db for a while, and I would like it to be resized back down to its 2mb default (with all of info retained) if it doesn't need to be 20mb. Does that make sense? Maybe I am not following your logic? – Liam Jan 11 '10 at 23:13
  • Updated my answer. Basically, there's no way apart from backup/restore. You could script this as a nightly/weekly task maybe? – Blorgbeard Jan 11 '10 at 23:19
  • Thanks! I have read that page /minus/ the bottom part that mentions the backup/restore. Makes sense though, with how complicated the pointers are within the paging system. Thanks for taking the time and effort to post, and point me to the backup/restore part of the article. – Liam Jan 11 '10 at 23:34
  • Can you not get similar results by forcing a Sweep using gfix? I thought a B&R was a last resort option. – Vivian Mills Jan 18 '10 at 15:11