mahendra.nepali@... wrote:

> Hi,
>
> I am facing an issue with the size of the firebird database file.
>
> I run a data upload process that loads data from series of XML files
> (around 70 GB+ data) into Firebird 1.5 Database via a .net application.
>
> The process takes atleast 10+days to complete but when I check the
> database file size, it is 5 times greater than the total xml file size.
>
> To speed up the process I load lots of data into temp tables and then
> delete the records from them for each xml file I load. However I think
> the delete doesn't shrink the database size.
>
> Then I tried backup and restore, where the database file size got
> reduced drastically.
>
>
>
> Can someone please let me know how I can reduce the database size when
> the delete is fired? This is because my application is going to receive
> daily data updates of minimum 40 MB and the same process is going to
> run. But that could cause concerns as the database size may exceed the
> harddisk size and every time a backup and restore will have a large down
> time on an online application.
>
>
>
> Regards,
>
> Mahendra Nepali
>

Hi Mahendra,

This is a FAQ, and has been answered a lot of times in this list, please
check the archives for a full coverage of the topic. I will be brief in
my explanation.

The database size is never reduced when one deletes records, the pages
that hold those records are marked as "available" so when new space is
needed there is no need to ask the OS for more space, it just uses the
already allocated and available space.

So if you insert 40MB of data in temp table, then transfer it to the
"official" table, and delete the records from the temp table, your
database you grow around 80 MB (40 MB for the temp, 40MB for the
official) then you delete all the records from the temp table your
database will not shrink in 40MB, but next time you insert 40MB in your
temp table again, the unused space will be used, so your database will
grow "nothing", and when you insert on the official table it will grow
around 40MB.

In the practice the numbers will vary of course, but rougly the only
increase in space will be the size of the new uploaded records not twice
as that.

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br