At 09:37 AM 28/02/2009, you wrote:

>> 1. Firebird 2.0.what?
>> 2. Classic or Superserver
>> 3. If Classic, are you running the Guardian?
>>
>To the best of my knowledge, we are running Classic

Check it and be certain! And, while you're at it, also check the page buffer size. If there's been a history of hopping back and forth between SS and Classic, chances are you're trying to run Classic with outrageously large cache settings.

>, and yes, Guardian service is running! (I inherited this mess)

Well, the first obvious thing, if you are running Classic, will be to stop both Guardian and fb_inet_server and do 'instsvc remove' on the Guardian to get rid of it.

>> Does the legacy software connect to the Firebird database?
>Assuming yes...
>> Does this imply that there are periods when the users of the .NET
>apps are not likely to be connecting/connected?
>
>Legacy software is constantly connecting/disconnecting from the
>database.

You originally said the legacy app was running 24/7/365.

>Unsure about the second question. What are you meaning?

You described your .NET parts as "patchwork". So it was just a guess that you were talking about multiple apps that might be connecting and disconnecting at random, in contrast to the legacy one that you said was 24/7/365.

>
>> It has the symptoms of a garbage collection problem. A full
>shutdown of the server would deal with long-running uncommitted
>transactions in that legacy software, which is your most likely
>offender, if I rightly understand that your "patchwork" apps are hit-
>and-run.
>
>I have come to the same conclusion about GC and/or Transaction
>release, but there seems to be no way for me to physically release a
>transaction in the 2.0 version of Firebird. We have just began to do
>a reboot of the server once per week, but even this doesn't guarantee
>that we won't encounter the problem during the week!

"Physically release a transaction" doesn't signify in this context. You've got something intrinsic in the workings of those applications that is causing this problem - not just one isolated transaction but something that's happening all the time. Only fixing the way your apps manage their transactions can prevent what's happening to your database.

The ability to stop a single rogue transaction, that comes with the MON$ features of 2.1, doesn't cure this sort of problem: you'd be chasing your tail forever. It's for when psychotic accountants are given the means to run crazy queries that never end.

> I have changed the sweep intervals, played with transaction
> properties, etc. based on other forums.

That won't cure it, either. Changing the sweep interval has no effect if there's no eligible garbage to sweep.

Set the sweep interval to 0 and explicitly use either gfix or gbak to do your global sweeps as a necessary part of your database maintenance until you fix your applications. For your conditions, do it when there's nothing connected to lock things up.

Well...actually...if you had long-running read-write transactions before, that were stuffing it up, changing them to read-only read committed *would* avoid some stuck garbage. But if it didn't remove the problem then you didn't catch the culprit, or at least not all of them.

Find out about those transaction settings, for both of your interface layers. Upgrade the ODBC driver. Use the firebird-odbc-devel and firebird-net-provider lists to find out how your connection and transaction configs might be causing this problem.
./heLen