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