SQLITE database size grows very large due to Change Tracking

Product(s): WaterCAD, WaterGEMS, HAMMER, SewerGEMS, SewerCAD, StormCAD, CivilStorm
Version(s): 10.02.XX.XX and greater
Area: Output and Reporting


The .SQLITE model database file is extremely large, even for a model with a reasonable number of elements and alternatives. For example 6 GB for a 7000 pipe model. This may make performance very sluggish and take a long time to open and save.


First, try compacting the database and purging records to save space, in case old records of previously removed elements and components are still stored in the database. This is done by opening the product without opening any models, then going to File > Database Utilities > Compact Database > Select Model > choose the Purge option. (Note: if you utilize the GIS-ID feature, you may need to purge additional elements from the "GIS-ID Center for Deleted Elements" - see this article for more)

If compacting the database does not help, This issue has been known to occur when the Change Tracking feature has been turned on and the user has performed bulk operations such as ModelBuilder imports and constraint based design runs. These types of operations can result in a large number of extra rows of data in the change tracking system (sometimes tens of millions of rows).

Starting with version, you can now archive change tracking data in both the water and storm-sewer products. This can help with situations where including change tracking data may otherwise increase the size of the model's database file to a great extent. See the section "Removing / clearing change tracking records" in the following article: Using Change Tracking for insights into model changes

Change Tracking Cleanup for older versions (advanced)

If you are using an older version, you can use the below information to manually clean up the change tracking database using the below information, or contact Technical Support (for example post in the Forum) to manually remove the information from the database.

Note: the following information is for advanced users who are comfortable with directly editing the model database. Care should be taken to back up your model before performing these steps. If in doubt, contact Technical Support.

  1. Download and install a SQLITE database editor. These instructions use SQLITE Expert Personal (version 5.3.4) as an example. Ensure that you use a 64-bit version, as this will be needed for large database sizes.
  2. Back up your model database - make a copy of the model's SQLITE file.
  3. Open a copy of the original SQLITE file (File > Open Database, in SQLITE Expert). Opening a copy of the original will ensure there is a clean backup in case anything goes wrong.
  4. Check the number of rows in the Change Tracking Table
    1. Ensure that table counts are displayed. In SQLITE Expert, go to Tools > Options > General and make sure the box is checked for "show record counts"
    2. Scroll down the list of tables on the left side until you see the table named HMIChangeTracking. The number of records should be shown in parenthesis next to it. This make take some time to load. Make sure you are looking at the correct database, SQLITE expert saves earlier opened databases as well. 
  5. Clear the Change Tracking Table
    1. Select the HMIChangeTracking table in the list on the left
    2. Select the SQL tab on the right (in SQLITE Expert)
    3. In the SQL tab, use the following SQL statement to delete all rows from the HMIChangeTracking table. If you see the file is read-only and not able to make changes, run SQLITE Expert as an admin. (right click on SQLITE Expert from start menu > run as admin). 

      DELETE FROM HMIChangeTracking
    4. For users with SQL knowledge: you can add a WHERE statement following HMIChangeTracking to limit which rows to actually delete.
    5. Click the Execute SQL button.
  6. Once the execution completes, go to Database -> Vacuum. This will essentially do a compact database and vacuum up all the now empty space after such a large deletion.  The vacuum may take a few moments to complete.
  7. Check the row count again - it should be zero if you used the above query to delete all rows.
  8. Close out of the SQLITE editor application, check the SQLITE file size, open the model and ensure that it is working properly.

See Also

Using Change Tracking

Compacting a database for optimal performance and general troubleshooting