At 20:49 10/10/2008, you wrote:

>> >I was under the impression that triggers and stored procedures
>> >automatically run in their own transactions and that they either fully
>> >complete or give an error and roll back.
>>
>> No. Triggers and SPs run in the transaction that called them. If
>the transaction doesn't finish for any reason then the work done by
>triggers and SPs remains unfinished. The work is committed if the
>transaction commits and is rolled back if the transaction rolls back.
>>
>
>Would it be correct then to say that all calls to the DB from my app
>should always be made within a transaction which I explicitly declare?

Certainly within a transaction that your application has complete control of. Since you are using IBO, you can (and should) do that *as a basic*. Off-topic here - use the IBO list.

>Can garbage collection be done by running a command, or can it only be
>done by restoring the DB?

Actually, that's not a good appreciation of either garbage collection or the use of restore. GC will happen as a matter of course if applications take good care of the transaction workflow. And, while it is a fact that restoring from a backup will get rid of neglected garbage, and thus can rescue your system from the undesired effects of long-running transactions, it is not designed for that purpose. When you find and fix the problem, you will never need to use it for that purpose.

Both backup and sweep will get rid of garbage if it is available to be released. However, neither of these will collect old record versions that remain interesting to a transaction. In your current situation, you might consider this as an experiment:
1. do a gstat -h, piping it to a file.

2. get everyone to log right out (perhaps at 5 p.m., when they are leaving anyway) - including yourself. Not power off, but properly log out of their applications. (If you are letting them log in as sysdba then lock them out of the rooms where their workstations are!)

3. put the database offline with gfix.

4. do another gstat -h and pipe it to a different file.

5. stop firebird and restart it.

6. as sysdba, do a backup or a sweep. (If you decide to do a backup rather than a sweep, don't restore it.)

7. now do another gstat -h and compare it with the ones you did before. You should be able to see the effects of a) just getting everyone logged out and then b) doing a "forced" GC without interference from those infernal users.

This exercise isn't anything like a "recommended housekeeping strategy" but it could be an instrucive one-off or occasional test to give you a feel for what the database engine can do if it is allowed to. It might be also turn out to be a short-term thing you can do every few days to stop the wolves howling until you have found and fixed the long-running transaction problem.

>> This looks perfectly reasonable for a database that is three weeks
>old. Is it slow and/or crashing at the moment? How many users were
>logged in when you took these stats? What were they doing?
>
>No the Db is performing Ok at the moment. Probably about 7 users were
>logged in, mainly reading.

Seven readers won't be creating any new garbage but they could well be holding open transactions that keep garbage interesting. It's all conjecture until you get into that code and figure out where you have long-running read-write transactions, e.g. grids containing datasets, CommitRetaining (kill!kill!). Don't overlook, either, the possibility that someone (you, even?) might be causing this problem by keeping a utility tool running constantly with an open read-write transaction, with or without CommitRetaining.

>> Are you using Delphi applications?
>I'm using Delphi 2006 & IBObjects TIBO components (v4.8.7).
>
>Perhaps I should write an idiots guide for people like me... :)

(Are you Mitch Landor?)

./hb