>btrumba wrote:
>>
>> I want to LIMIT database size. One user is using database. E.g.: let
>> say that user can only insert 100Mb of data into database.

At 03:10 AM 19/12/2007, Woody wrote:

>The problem with size is that it's not an indication of the actual
>amount of data. Deletions only remove the record from view, not from the
>database file itself. That only happens when you backup and restore.

Woody, that's not true. I think what you intended to say was that deleting records does not reduce the size of the database file. Like the back versions of updated records, those of deleted records are garbage-collected once they become uninteresting.

What btrumba needs to understand is that Firebird stores the older versions of changed records and the deleted records for as long as it takes to keep them available to other transactions that are interested in them. The length of time that the back versions survive depends on how long those other transactions take to lose interest in them.

Once a back version becomes uninteresting, the disk space it has used becomes available for re-use. Backing up the database and replacing it with a restored version gives you a freshly laid-out database with no spare pages.

Perhaps btrumba does not know that a Firebird database file is not a folder full of hidden physical files storing physical tables of data and indexes, like Access and some other desktop databases. It is a single logical file containing data laid down in a compressed format on chunks of disk that are controlled by the Firebird engine. These chunks are known as "pages". As Ann explained, there are many types of pages, apart from data pages. For example, there are index pages that store the node structures of the indexes, cascading pointer pages for index and data pages, pages for storing BLOBs, there's a page to store transaction inventory, and so on.

So, while you could calculate the actual size of data by counting bytes across the row structure and multiplying that by the number of rows that are visible to a particular transaction, that does not tell you anything about the size of the database itself.

It is not uncommon for databases containing relatively small amounts of "actual data" to be bloated to 2 or 3 times their minimum size due to badly written applications that prevent the Firebird engine from identifying and removing obsolete back versions.

./heLen