Database Sweep Interval Setting To Improve Performance
Overview:
Garbage Collection:
This is the ongoing cleaning of the database and is performed in the background around the clock. This constantly reorganises the memory space used by the database. If you don't clean up the database performance will slowly but surely degrade.
When performing a garbage collection, Firebird does nothing other than remove outdated data sets and index files, which results in a smaller database. Outdated data sets are stored by Firebird for the following reason: Firebird is a multi-generational databases. When a data set is altered, this alteration is stored in the database as a new copy. The old values remain in the database as a back version, which is the rollback protocol. If the transaction is rolled back after the update, the old value is ready to resume its function as the valid value. If the transaction is however committed, and not rolled back, this back version becomes superfluous. In databases with a lot of update operations this can result in a lot of garbage.
What is a Database Sweep? The sweep facility runs through the database and removes 'garbage'. This prevents the database from growing too big and helps reduce the time it takes to start a new transaction on the database. The potential downside of this process, is that it can affect everyday performance of the database whilst the Sweep process is running when users are accessing the database. A default sweep interval of 20000 records is defined for all databases initially. This means once 20000 garbage records have been stored, (on some databases this may occur frequently during the day) the database will automatically initiate the sweep routine to purge these unwanted records. This process can then cause the database performance at that moment to be adversely affected. This is potentially why a database may run slow sometimes and then suddenly perform well.
Amending the Database Sweep Interval Setting and Managing the Sweep outside of the automated process (Recommended):
Stop the sweep process entirely (by setting the Sweep Interval to Zero) and manually manage the Sweep process by manually initiating the Sweep at a time when users will not be affected.
Refer this article describes how to manually invoke a Sweep
Important:
Irrespective of how frequently the sweep process is run, you must run a regular Firebird Backup and Restore which will not only remove the garbage but also maintain table Indexes, database error checking and re-compact the database size.
For more information on Sweep please refer to this article http://www.firebirdfaq.org/faq312/
Determining the Sweep Interval and Amending it:
From Build 266 of the Ostendo Update 242 executable file, the sweep interval setting specific to the database you are logged onto is displayed from the Ostendo About screen. (Help -> About)
Alternatively Download the attached Command Script.
Run this command script to determine the current Sweep Interval setting. If the setting is greater that 0, then proceed with the actions below to amend that setting.
Modifying the Database Sweep Interval Setting:
You must have exclusive access to do this database. Ensure you stop any Ostendo API, Web or Queue Services before amending this setting
Run cmd (Run as Administrator)
Define the variables USER and PASSWORD, it makes life easier, in that you do not have to type in the user name and password every time you issue a command. Substitute the Firebird 'bin' folder path appropriate to its location on your server.
cd C:\Program Files\Firebird\Firebird_2_5\bin
SET ISC_USER=SYSDBA
SET ISC_PASSWORD=masterkey
Substitute the Firebird 'bin' folder path appropriate to its location on your server along with database path and name as necessary
cd C:\Program Files\Firebird\Firebird_2_5\bin
gfix -h 0 c:\Database\Ostendo.fdb
Now check to see the Sweep Interval was change successfully by logging back onto Ostendo and re-checking Help->About
Finally run the dbutiles.exe to perform the following:
- Validation
- Backup
- Restore
Related Articles
08/05/2025 - Database Sweep Interval Setting To Improve Performance
Database Sweep Interval Setting To Improve Performance Correction to previous Article (Please Note: This is an amended version to the previous article on Sweep Interval. There was a syntax error in the DOS commands) On occasions sites can suffer ...Manually Running a Database Sweep
The default database sweep value is 20000. If this value has been amended to Zero, it will be necessary to empty out accumulated 'garbage'. This can be achieved by either performing a Firebird Validate / Backup and Restore of the database, or ...Increasing Firebird Performance
Below are links to two articles relating to potentially increasing Firebird Performance https://ib-aid.com/en/articles/how-to-make-firebird-work-20-faster-at-windows-server-2016-in-less-than-1-minute/ ...Firebird Restart to improve performance and remove External Users
Firebird Stop / Start process Large Ostendo sites running 20+ users should stop and re-start Firebird on a regular basis to ensure performance is maintained. The major issue to automating this process in the past has been the worry that users may ...Improving Performance of Opening Lists
As a database gets larger, the opening List grids can take longer to load, when 'Include Closed' Orders, 'Include Updated Status' or 'Include Fully Paid' is turned on along with Filtering and Sorting from the Grid options. This is because in order to ...