At 09:50 PM 7/01/2008, you wrote:

>Here your are J

Would you please not top-post?

>At 09:33 PM 7/01/2008, you wrote:
>>Could be these errors be a reason to make a database file extremely slow?
>>
>Did you run gstat -h as suggested? Send the results.
>
>Database
>"c:\fd\customers\pos-veneti\works\071215_egnatia_lags_on_ekdosi_arithmou\new
>_ble_eg_pos-before-backup.fdb"
>
>Database header page information:
>
> Flags 0
> Checksum 12345
> Generation 4914769
> Page size 1024
> ODS version 11.0
> Oldest transaction 4897071
> Oldest active 4914740
> Oldest snapshot 4914740
> Next transaction 4914765
> Bumped transaction 1
> Sequence number 0
> Next attachment ID 0
> Implementation ID 16
> Shadow count 0
> Page buffers 0
> Next header page 0
> Database dialect 3
> Creation date Nov 16, 2007 13:28:27
> Attributes force write
>
> Variable header data:
> *END*
>
>>The 'extremely slow' database file is while you ask records from a table of
>>2000 records and makes 4 minutes to return the first 30 records (not
>fetch).

~ 95,000 transactions per day and you have ~17,700 gap between the oldest interesting and the oldest active. Your OAT has remained the same on at least the most recent two garbage collections....so you have some long-running transactions there. Quite possibly you are getting "peaks" in heavy accumulations of garbage that are just making things slower and slower. This could even cause the server crashes.

The source of such problems is generally in the application code. However, if you have composite indexes (including composite keys) then degradation in the currency of the index statistics could be an issue.

You said:

>>The errors 10061 & 10054 appeared in the log file 10 times each one in one
>>month.
>>

Those two errors recurring 10 times in one month, without evidence of the server crashing, could well be just because someone is rebooting the server without warning. Maybe you have a user who thinks rebooting might speed up these slow queries...or it could be a bad or misconfigured network card or intermediate router.

Is the Firebird server being run on a machine that is being used for other things? Does this person understand what will happen to the database clients if s/he reboots the machine? Or uses some software that hangs or crashes the machine?

1. Do some investigation of user behaviour. Find out what's really going on there.

2. Run SET STATISTICS on the database to update the index statistics.

3. Back up the database and do a safe restore.

4. Replace the existing database with the safely restored one and watch to see how those slow queries are affected.

5. If you see things starting to slow down again then make SET STATISTICS a daily ritual and backup/safe restore a weekly one, until you have nailed down the problems in the application code. (Prime suspect: a Delphi application using CommitRetaining).

./heLen