Compacting a database for optimal performance and general troubleshooting

Product(s): WaterGEMS, SewerGEMS, CivilStorm, StormCAD, PondPack, StormCAD, HAMMER, WaterCAD
Version(s): CONNECT Edition, V8i
Area:  Other


This article describes how to compact your model database in the OpenFlows products, to save space (reduce file size), increase speed, prevent possible corruption, and as a general troubleshooting step.

In addition 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 such as those that result in the error "object reference not set to an instance of an object" (though this particular generic error can occur for a number of other reasons).

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


If you have made a large number of changes to your hydraulic model (and therefore its underlying database), 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 (although this is highly unlikely with the CONNECT Edition which uses SQLITE files for the model database). 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 the software provides 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.


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 File > Database Utilities > Compact Database. In the older, V8i releases of the products go to Tools > 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.

  4. (if you have utilized the GIS-ID feature with ModelBuilder) the above compact operation will leave deleted elements so they can be restored (e.g. ModelBuilder supports this). In this situation if you wish to clear these records to reduce the file size and increase performance, go to Tools > GIS-ID Center > GIS-ID Center for Deleted Elements and delete records as needed (multi-select and use the delete option). An improvement may be added to a future version to either provide the option to clear this data during Compact, or guide the user to the "GIS-ID Center for Deleted Elements" (reference # 932403)

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