Just to let you know we found the issue and solve it, it appears to be "a bug or glitch" that allowed us to add "not null" fields to a table after an existing SP was compiled to update and in some circumstances insert records into the same table. When re-compiling the SP we noticed the compiling error, and traced it back to the new fields on the table, however Firebird never complaint when we added the 2 fields as NOT NULL regarding the existing SP. I am not sure if Firebird runs a "validity check" when adding fields to a table regarding existing dependencies, perhaps it is a grey "philosophical" area as adding fields should not cause issues but in this case the DB was growing in size constantly until we re-compiled the SP. Let me remind you the SP was there to update the table, not to insert records (99.999% of the time). Any-ways, the issue disappeared once we re-compiled the SP
Regards and thanks to all the people that assisted with the diagnosis, Fabian --- In [email protected], "fabianchocron" <fabianch@...> wrote: > > Hi All, > > We are experiencing a very strange behaviour with FB 2.1, we have a DB with > just 1 table, and 1 SP to update the table. There are very few records on the > table, and we run updates on it every minute. We noticed the DB size keep > growing at about 10 to 20 mega per day. From memory in 1996 Microsoft SQL > Server was performing SQL updates on tables by adding a new record with the > changes and deleting the previous record. Assuming FB is doing the same, that > explains why the DB is growing, however we are wondering if there is any way > to set FB to "re-claim" deleted records and re-use that space. At the moment > it seems FB keep allocating new disk space for each update and it is not > re-using the free space containing the old updated records. The only way we > can reduce the DB size is backing up the DB (taking it off line) and then > restoring it on top. That works but takes the DB off-line for 2 minutes. If > we do not do the backup and restore, the DB response degrades every day, to > the point where it becomes too slow. We would not be surprised if we had > many records been added to the DB every day, or if we were running a > combination of deletion and inserts, however as previously explained all we > do is update about 10 records every minute, and that is all the DB holds, > there are no other records or tables in the DB. > > We would appreciate if you can give us a workaround, or a different approach > to avoid the DB growing in size and consequent slow response. > > Regards > Fabian >
