Subject Re: [firebird-support] Oldest transaction stuck
Author Thomas Steinmaurer
> I have an FB 2.1.2 classic installation running a 70GB database with
> the sweep interval set to 0 that runs gbak every night. I just
> discovered last week that gbak was being run without garbage
> collection (-g), probably to speed up the backup process. This led to
> a gap between OAT and OIT that was just huge.
> I successfully ran gbak with garbage collection (that took 48 hours).
> However, the transaction stats on the database do not seem to have
> been cleared up:
> Database header page information:
> Flags 0
> Checksum 12345
> Generation 24601590
> Page size 8192
> ODS version 11.1
> Oldest transaction 12778
> Oldest active 24126761
> Oldest snapshot 24126761
> Next transaction 24152614
> Bumped transaction 1
> Sequence number 0
> Next attachment ID 448968
> Implementation ID 16
> Shadow count 0
> Page buffers 2048
> Next header page 0
> Database dialect 1
> Creation date May 2, 2009 22:22:39
> Attributes force write, no reserve
> Variable header data:
> Sweep interval: 0
> *END*
> My understanding was that I do not need to run a manual sweep if I am
> running gbak each night (with garbage collection enabled, of course).
> The question is, do I need to run a sweep on this DB, or is there
> something else that is preventing the OIT from advancing?

If a sweep doesn't help (ideally, it should be run when there is no load
on the database), then it's probably a transaction in "limbo". Check out
gfix for checking a database for transactions in limbo.

> The server has been rebooted a number of times since the database was
> last restored from a backup (which occurred more than a year ago). It
> is difficult to find a maintenance window with this size database to
> do a full backup/restore cycle, so I'm looking for any other
> alternative to keep this healthy.

"No reserve" in the attributes isn't good either, because in this case a
data page doesn't have space for back record versions then. While you
can remove the no reserve attribute on a live database, already existing
data pages are still filled with this option in mind.

The only way to change this is to:

- Remove the no reserve option from the database
- Run a backup/restore cycle

I can imagine that running a full backup/restore cycle on a 70GB
database needs a rather largish maintenance window, so the following is
a rather wild idea but might work, although I have to apologize for the
IB LogManager plug here:

- Take the database offline and run the prepare wizard and the redo
wizard of IB LogManager
- Start a backup on the database by using the -g switch. The backup runs
in a snapshot transaction
- In IB LogManager: Register the entire database for logging
- Now, you can allow to let user connect to the database again. DML
operations are logged.
- Run a restore of the backup into a different database
- When done, run IB LogManager and define a redo product pointing the
source database to the production database and the redo database to the
restored database
- Use IBLMRedo_cmd to process the redo project, which basically re-apply
logged operation onto the restored database

I haven't tried, but it *might* and you have an offline window which is
very small, namely when taking the database offline for running the
prepare wizard of IBLM.

With regards,

Thomas Steinmaurer
Upscene Productions

Download LogManager Series, FB TraceManager today!
Continuous Database Monitoring Solutions supporting
Firebird, InterBase, Advantage Database, MS SQL Server
and NexusDB!