Hi,

I've already done a backup restore as Helen suggested, and these are
the results:

SQL> show database;
Database: /var/firebird/db/erp_sd.fdb
Owner: SYSDBA
PAGE_SIZE 8192
Number of DB pages allocated = 567584
Sweep interval = 20000
Forced Writes are ON
Transaction - oldest = 2283
Transaction - oldest active = 3464
Transaction - oldest snapshot = 3432
Transaction - Next = 3587
Default Character set: NONE
SQL>

The outcome is really yet to be determined. Its 3 a.m, and users
aren't up from bed yet. Thanks again.

> As Alan suggested, this is a very DIRTY database. It has uncollected
> garbage going back to the year "dot" (see a difference of more than 33
> MILLION between the oldest [interesting] and the oldest active
> transactions). Somewhere in there you have an enormous gap of who knows
> what rolled-back, deleted and limbo garbage. Then, between the oldest
> snapshot (the transaction following the "high-water mark" when garbage
> collection was last done) and the oldest active (i.e. uncommitted)
> transaction there is a gap of 1900 - that's approximately how many recent
> transactions are in an uncommitted state.
>
> Then, between the oldest active and the next one which will happen is a gap
> of about 170,000 transactions. That means you have that many transactions
> piling up data in the transaction inventory manager in memory (the cause of
> the server crashing) and a great deal of garbage accumulated on disk.
>
> To fix the database now, I suggest either an immediate sweep. That is
> going to take quite a while, possibly days if that first gap represents a
> huge amount of garbage. Then do the stats on the database to see what
> improvements you have. Set the sweep interval to 20,000 (use gfix -h
> 20000).
>
> Alternatively, do a backup with the -g switch (to prevent garbage
> collection) and restore -c[reate_database] to a new database file. It's
> going to take a long time, too, but it might actually be faster than a
> sweep in your case, and you will have a squeaky-clean new database
> afterwards.
>
> As Alan suggested, look hard at your application code. There are a LOT of
> transactions not getting committed in there. It's the worst set of
> transaction statistics I've ever seen.
>
> Until you get your head around your application problems, for heaven's sake
> set the sweep interval to around 20,000, schedule a weekly (or more
> frequent) backup WITHOUT the -g switch AND plan to sweep that database once
> a day.
>
> ./heLen
>
>
>
>
>
> Yahoo! Groups Sponsor
>
> ADVERTISEMENT
>
>
> ________________________________
> Yahoo! Groups Links
>
> To visit your group on the web, go to:
> http://groups.yahoo.com/group/firebird-support/
>
> To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>
> Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.

--
There are many ways of going forward, but there is only one way of
standing still.