Hello,

> My config is
>
> Windows XP Pro
> Firebird SS 2.1.2.18118
>
> I have a rather large database of around 110M records in 1 table.
>
> I needed to do some housekeeping, namely a backup/restore due to the DB starting to slow down and I also noticed a large difference in the oldest transaction and Next transaction (indicating garbage) when I did a gstat -h on the database file
>
> to backup, I use the following command
>
> gbak -v -t -user SYSDBA -password "masterkey" DBNAME.FDB DBNAME.FBK
>
> this normally takes around 30-45mins.
>
> Then after this completes I do a restore using the following command
>
> gbak -c -v -p 8192 -user SYSDBA -password "masterkey" DBNAME.FBK DBNAME2.FDB
>
> The restore takes around 3 hours.
>
> The last backup/restore I performed less than 1 mth ago.
>
> I once deleted some 6mths of data from the database and the restore process took around 4 days to complete, however, the backup was still rather quick at about 30-45 mins.
>
> My problem is that this time, the backup/restore is yielding unexpected results. The backup stage of the housekeeping ie
>
> gbak -v -t -user SYSDBA -password "masterkey" DBNAME.FDB DBNAME.FBK
>
> is still to complete after running for about 5 hours and the gbak command is stuck (or at least it appears that way) in the following line
>
> gbak: writing data for table SMDR
>
> SMDR is the large file. Normally I get
>
> gbak: writing data for table SMDR
> and after a minute or 2 the records are written ie
> gbak: 20000 records written
> gbak: 40000 records written
>
> and so on until the records have all been written.
>
> Task manager shows low CPU usage ie<3% very infrequent spikes to 15%,
> Can anyone tell me why the backup command is taking so long? Have I reached a FB limitation?

If it is the backup which needs that long then it possibly is the
garbage collection, which takes place in the source database.

If you actually will replace the production database with the restored
database, then use the -g switch in the backup process to omit garbage
collection in the source database.

Another thing you could try is to use the service manager way to execute
the backup and restore.

> Please let me know if I need to supply any more information
>
> gstat -h has the following
>
> Flags 0
> Checksum 12345
> Generation 25467545
> Page size 8192
> ODS version 11.1
> Oldest Transaction 3017525
> Oldest active 25467536
> Oldest snapshot 25467536
> Next transaction 25467537
> Bumped transaction 1
> Sequence number 0
> Next attachment ID 5627
> Implementation ID 16
> Shadow count 0
> Page buffers 0
> Next header page 0
> Database dialect 3
> Creation date Jan 5, 2011 19:12:30
> Attributes forced write
> Variable header data:
> Sweep interval: 20000
> *END*

With a sweep interval of 20000, I sweep should have been kicked in and
if it has, then it couldn't have done the job properly due to active
connections/transactions.

In this situation, you could set the sweep interval to 0 (disable
automatic sweep) and run it scheduled every night.



--
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!