Pack a Firebird Database

KB039 – Packing a database to maintain reliability and optimal performance

Packing a database on a regular basis will ensure database reliability, maintain optimal performance and may also repair minor errors. The pack process is split into 2 parts, a backup and then a restore. Each step is automatic and forms part of the whole process. Once a pack has been started, no further interaction is required.  Approximately 15-30GB per hour will be processed however this is dependent on disk and hardware speeds and the configuration of the server.

It is recommended that a pack is performed between 1-3 months. The higher the database usage the more frequently the pack should be performed.

This article is only applicable to installations that use the Firebird database software.  The database can be packed manually when logged into Volume, or from a command line which can be incorporated into a batch file and scheduled task to automate the process.

Prerequisites

Firebird must be on version 2.5.2 or above. Details on how to upgrade Firebird can be found at http://watermarktech.co.uk/knowledgebase/firebird-server-upgrade

Volume must be on version 5.5.1.4 or above on the server and clients. Contact support support@watermarktech.co.uk for details on how to upgrade Volume.

Temporarily disable the backup scheduled task which triggers the watermarkbackup.bat batch file before packing the database.

Disable any other scheduled tasks that may exist which interact with the Volume database.

1

To pack a database manually in Volume, first log into Volume as the superuser.


2

Select ‘File’ and then ‘System Setup’.


3

The first part of the pack process is the backup which copies all data held in the database to a separate temporary file.  The default location for this file is c:\temp\ however this can be altered. The location must have enough free space to hold a copy of the database.  A pack can be performed quicker if the location is held on a separate internal disk to that of the Volume database.  For optimal performance it is recommend to always have the path pointing to a location on the server.  The location can be specified in the below field.


4

Select ‘Pack’ to begin the pack process.


5

Read the confirmation message and click ‘OK’ once happy to proceed.


6

Once the pack has started a status window is displayed. System usage for users will not be interrupted during the backup part of the pack process, although it is recommended that users do not use Volume whilst the pack process is running.  When the pack process begins the restore part of the pack process, all connections to the database will be closed. The pack process will forcibly terminate all connections that are open and users will be disconnected from Volume.


7

Once the pack is complete, the information contained in the status window is written to a log file (wnp_pack.log).  This log file is stored in the same location as where the temporary file is stored during the pack process.

Automating the Pack Process

The pack process can be automated using a batch file and scheduled task.  Volume needs to be on version 5.5.1.4 or above.

1

Create a new batch file called watermark_pack.bat.

Edit the batch file to: –

1) Temporarily disable the backup scheduled task which triggers the batch file watermarkbackup.bat.

2) Temporarily disable any other scheduled tasks that may exist which interact with the Volume database.

3) Trigger the pack.

4) Re-enable the backup scheduled task which triggers the batch file watermarkbackup.bat.

5) Re-enable any other scheduled tasks that may exist which interact with the Volume database.

In order to trigger the pack in the batch file, include the following line for the relevant version of Windows: –

32 bit version of Windows – “c:\program files\watermarktech\volumeFINANCE\volumeFINANCE.exe” /pack

64 bit version of Windows – “c:\program files (x86)\watermarktech\volumeFINANCE\volumeFINANCE.exe” /pack


2

To calculate the length of time required to perform a pack, run a manual pack first using the instructions provided above.  The pack log file (wnp_pack.log) will indicate the start and end times.


3

Create a scheduled task to run the watermark_pack.bat after the Volume backup has completed. Make sure it does collide with the backup process batch file (watermarkbackup.bat).


4

Regularly monitor the backup and pack log file to make sure they are completing successfully.

Leave a comment

Your email address will not be published. Required fields are marked *

For more information call us on +44 (0)1274 535090 or email us at info@watermarktech.co.uk

Accreditations