--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> At 06:18 PM 4/02/2011, timwilsonaus wrote:
> >Have a database that for some reason overnight will double or triple in size from about 200MB up to about 600MB.
>
> Not "randomly", though. It will grow in size as new pages are allocated from the filesystem. The engine asks for those pages because it needs them.
>
>
> >The DB is an FDB format and running on a server with the latest 2.5 Firebird version installed.
> >
> >Running a Backup & Restore on the DB returns it back to it correct size. But I need a more permanent fix.
>
> No, restore "returns" it to the minimum number of pages needed to store all the data. As soon as you update or insert anything, it will grow.
>
> >I have tried to monitor the DB size over the last few weeks and the DB size continues to spike.
>
> No, it won't "spike", since the database size doesn't get smaller - ever - except if it is totally recreated by a restore. You are probably getting garbage buildups but something else could be at play here...if you have it configured to "use all space" (not recommended for any but a read-only database) then there's a high chance that existing pages with released space won't have room to re-use such space a lot of the time. Do check that one (gstat -h and check the attributes).
>
> >However I can see no pattern as to when this seems to occur or what might be causing it.
>
> If the growth seems sudden, it will be from a bulk insert or update. The bulk insert shouldn't cause greater growth that the actual size of the data. A bulk update will effectively double the space used if your applications are handling transactions in such a way that the engine can never clear garbage.
>
> >Has anyone had similar issue
>
> The *issue* isn't uncommon and the "Use all space" attribute is often behind it.
>
> >or any suggestions as to what might cause this?
>
> Far, far more common is applications not committing transactions in a timely manner. A traditionally *very common* cause of that is Delphi applications that use CommitRetaining...which was an abomination designed by Borland/Inprise to "hide" transactions from programmers who only understood non-transactional data systems.
>
> ./heLen
>

---------------------------------------------------------------------

Hi Helen

We ran gstat – before and after performing a backup restore (Image on left is after restore). Most of the statistics are very similar when comparing the reports, except for the clients table – in particular these two indexes in the table. For whatever reason its showing a massive variance between the total number of rows that share duplicate indexes (Total Dup) and Max Dup.

Gstat Image Link: http://imgh.us/Comparison.png

We have a data refresh process for our clients which pulls in contact information via an ODBC connection from an external Database. If anywhere this would be where the issue is stemming from, however it seems to only affect this one particular clients Database. The Client also complains about slowness when users are searching for contacts…

Is there is any way to fix this problem without having to do a backup restore every time?