--- In firebird-support@yahoogroups.com, "Adam" <s3057043@y...> wrote:

>
> --- In firebird-support@yahoogroups.com, "Craig" <cstevens@d...>
> wrote:
> >
> > 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
> with
> > 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 ?
>
> Craig,
>
> 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.
>
> Rule:
>
> 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.
>
> Adam
>

Thanks Adam,

Thats nice and clear to me now.

Cheers
Craig