Hi Pepak!

Things are both simpler and more complex than you think! All tables are
stored in the same file (well, normally) and whenever Firebird needs
more space than is available in the file, it asks the operating system
and the file size increases. The file size NEVER decreases, Firebird
itself keeps track of available space within the database file and
reuse space from deleted records rather than hand the space back to the
file system and ask for it again (it is much quicker to do things this
way). Even a table that you've deleted may be the reason that your
database is bigger than necessary!

If you backup and restore, the resulting database file may be smaller
than the original database. I think that is the only way to 'reduce
database file size'. That should be pretty simple and easy to do.

Though one further thing. If your database seems to grow uncontrolled,
it may indicate a problem in your transaction handling. Whenever you
update or delete a record, what happens isn't that the original record
changes, but rather Firebird writes a new version of the record to the
database (I guess it writes changes rather than the entire record, but I
don't know the internals). Hence, you get two versions of the same
record. The old version may still be what some transactions are allowed
to see, and the space cannot be reused until there are no longer any
transactions that can potentially have this old version visible. So, if
you have one transaction that just looks at your data and don't ever
commit, and then other transactions modify each record, say, on average
5 times, you may in theory get a database 6 times the size that is
necessary even though it is only this one readonly transaction that
doesn't commit properly! Moreover, you may get performance trouble with
lots of versions of many records. Running some statistics may reveal
that transaction handling is the problem.

HTH,
Set

penwincz wrote:

> (I apologize in advance if you get this message twice - I sent the
> first one before actually subscribing to the group and I am not sure
> now if it will actually make it into the list)
>
> I would like to reduce the size of my Firebird 1.5 database. In order
> to do that I need to know on which tables I should focus. Is there
> some way to find out how much space is a table and its associated
> metadata (such as indexes) taking up? I thought I could get at least
> the data size (not index size) by reading a number of pages from
> RDB$PAGES and multiplying it with page size, but the results seem to
> be way off ("SELECT count(*) FROM rdb$pages" only lists about 600
> pages for a 700+ MB database with page size of 4 KB). Is there some
> better way? I only need to do this once or twice, so a standalone
> application that would list this information for me would be just
> fine, although I would prefer to be able to read this information
> from the database from my application.
>
> Thanks, Pepak