Subject Re: Database Size + OH NO ! not Sweeping troubles again
Author Adam
--- In, "Craig" <cstevens@d...>
> Hi All,
> Please correct me if i am wrong, but if i have a database that
> contains many records and one table is a list of blobs {data mainly
> jpegs} that are deleted from time to time from the table, the
> effective size of the database reported by the OS would decrease
> the deletion of this blob data. I have a database which i add data,
> then later remove the jpegs from the blob table once i buyrn to cd.
> However the database does not seem to reduce in size.
> I have done some reading on Sweeping etc etc and this is what the
> database is reporting to me
> Sweep Interval = 20000
> Forced Writes are on
> Transaction - oldest = 24112
> Transaction - oldest active = 24113
> Transaction - oldest snapshot = 24112
> Transaction - Next = 419254
> I ran a manual sweep on the database that does not seem to have had
> any impact. My question is am i wrong about the database resizing ?
> and does the data above tell me something that i should be aware
of ?


What is wrong is your understanding on how the allocation of disk
space happens. There are two different things here.

How much storage space that the Operating System has granted
Firebird, and how much of that space Firebird is using.


Firebird never relinquishes the space the OS has granted it. That
would require a lot of housekeeping and a defragmenting for space
that there is a pretty high chance will be required again sometime.

Once the deleted records (in this case BLOBs) are no longer
interesting to any transaction, they can be garbage collected. The
garbage collection allows Firebird to re-use the space these deleted
records were stored in when it needs to store data in the future.

Firebird stores data in "pages". When a new page is required, if
there is a previously deleted and garbage collected page available,
that will be used. Otherwise, it will request more space to the
Operating system. Firebird is able to re-use both entire pages and
also slots within the page.

Generally, databases will grow rapidly until they hit an equilibrium,
and then the file size growth is proportional to the rate of growth
of data.

The only way to shrink the file size back down is to backup then
restore the database using gbak. But this is not a long term
solution, it will simply grow again.