> 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
http://www.upscene.com
http://blog.upscene.com/thomas/

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