Subject Re: [firebird-support] database size remains constant after deletion of records
Author Ann W. Harrison
Deepak Gupta wrote:
> but in my case after calling sweep when i insert new
> record in database, database size is still increasing and
> I think it is just becouse of that data is not inserted
> on free memory blocks that freed by sweep.

Here's how space is reclaimed after a record is deleted

1) Transaction 101: Delete records

Internally the database creates new stub versions of the
record containing no data, but carrying Transaction 101's
identification and marked as delete.

So, the first effect of deleting a record is to make the
database bigger.

2) Transaction 101: Commit

Even though new transactions and read-committed transactions
are now aware that the records were committed, running concurrency
transactions still see the old version, so the space cannot be

3) All other active transactions commit or rollback

Now, there are no more transactions that can see the old version,
so it becomes "garbage"

4) One of the following happens:

4a) Some transaction tries to read the deleted record through a full
table scan or an index range retrieval, or a direct index lookup

4b) Sweep happens

4c) Gbak runs without the -g switch\

Any of those actions will remove the deleted record stub and the
deleted record and make the space available. That space will be
used before the file is extended.

Step 3 is important. While there are active transactions that might
need the old record version, that version doesn't go away.