Compacting a database for optimal performance and general troubleshooting

Product(s): WaterGEMS, SewerGEMS, CivilStorm, StormCAD, PondPack, StormCAD, HAMMER, WaterCAD
Version(s): 10.XX.XX.XX, 08.11.XX.XX
Area:  Other

Problem

This describes how to compact your database in the OpenFlows line of water and wastewater products in order to save space, increase speed, prevent possible corruption, and as a general troubleshooting step. In additional to compacting the database as a troubleshooting step it helps to delete the output files that are stored in the folder where the model files are located. There is more information about deleting output files that can be found here.

This solution also works for some issues that result in the error "object reference not set to an instance of an object".

For cases where the SQLITE database file is very large due to Change Tracking, see: SQLITE database size grows very large due to Change Tracking

Background

If you make a lot of changes in a database and do it often, portions of the database may become fragmented. Fragmentation can lead to issues where the database does not function optimally and over time this can cause performance issues or possible corruption. Three of the top reasons to compact the database and purge record regularly are 1) Save space 2) Increase speed of processing 3) Prevent possible future corruptions.

For this reason we offer a two ways to maintain the health of your project database files. The first is by automatic database compaction based on the number of times you open the database file via the File > Open function. The second is the manual option.

Solution

Set your software to automatically compact the database file after opening it a given number of times

*Please note that by default the software is set to compact databases automatically after every 10 times you perform a File > Open.  

  1. Open a project file or start a new project

  2.  Go to Tools > Options

  3.  On the Global tab under the General section (first section) you should see a check box that says "Compact database after". To the right of that you are given the option to set the number of times.

  4. After you are done click the "Ok" button to enable the changes

Manually compact the database

  1. Open the software but, do not open your project file. This can be done by clicking on the application icon and selecting close on the Welcome window.

  2. Go to Tools > Database Utilities > Compact Database. In the CONNECT Edition releases of the products go to File > Database Utilities > Compact Database

  3.  In this next step you are presented two options: 1) Retain record to optionally allow elements to be completely restored later 2) Purge records to save space. Option 2 for purging records will work best to help the database functional optimally as it reduces the number of unnecessary records saved.

See Also

Large number of output files being saved

SQLITE database size grows very large due to Change Tracking

Compact and repair large database with Microsoft Access

Anonymous
Recommended
Related