Your oldest transactions (16339, 16340 and 4656) are way lower than your next transaction. In practice, this probably means that most of what has ever been done in your database (since last restore), still has to be kept. What you have deleted cannot be removed from the database, because the data still has to be visible to transactions that started before the transaction that deleted the record got started - and the same applies to updates. If a record has been modified 1000 times, then any query touching this record may have to look through 1000 versions of that record, and that may be time consuming.

Now, your gap may or may not be a problem - if you commit between each delete and delete 150000 records, then the oldest transaction may still only be a few minutes old and after all old transactions commit, things are likely to become faster again. On the other side, it may be a sign of poor transaction management, and if so, the database will appear like sluggish and unpredictable until the transaction management is taken care of. No-one but you may know the answer to this (well, possibly excepting people knowing EMS-Manager, I don't).

Note that the gap may be due to any transaction, I'd even say that whilst a massive delete is likely to be what makes poor transaction management visible, the old transactions that you have to find is likely to be completely different transactions - they may even be transactions where you only select and do not update anything (read-only AND read committed transactions should be no problem, but selects in other type of transactions are).

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Olaf Kluge
Sent: 17. februar 2009 08:49
To: firebird-support@yahoogroups.com
Subject: AW: [firebird-support] damaged database



Hello,

thanks for your help.

The bad statistic I have after deleting many records. The EMS-Manager asks
me after each step - commit or callback - and I have committed. I think, a
large part of never ending transactions is a result of deleting data.

Thanks.

________________________________________
Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] Im Auftrag von Thomas Steinmaurer
Gesendet: Dienstag, 17. Februar 2009 08:42
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] damaged database

> Sadly has no one an idea for my latest problems.
>
> Perhaps, you can help me with this question.
>
> ClassicServer 2.01. After I have deleted much data once more, the
> gstat-monitor shows me the follow:
>
> Oldest Tr. 16339
> Oldest active 16340
> Oldest snapshot 4656
> Next transaction 198659
> Bumped trans. 1
> Sequenceno. 0
> Next attachmentid 428
> Implementation id 16
> Shadow count 0
> Page puffers 0
> Next header page 0
> Database dialect 3
> Creation date feb 12, 2009 20:29:52
> Attributes force write
> Sweep interval 20000
>
> The deletion I have made with IB Manager 3. I have committed all steps and
> close the program after action.
>
> What does the snapshot counter mean? Can I manually update the counters?
> (oldest transaction)

AFAIK, the oldest snapshot is the oldest active transaction at the time
of the start of a snapshot transaction.

Your statistics simply tells us that you have a long-running/active
transaction somewhere. This can be either a:

* not ended transaction
* ended transaction with using commit retaining
* transaction stuck in limbo (something went wrong during a distributed
transaction)

--
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions
http://www.upscene.com
My blog:
http://blog.upscene.com/thomas/