Ultralite Database Size Fix (shrink)?

[Former Member]

Hi all!

I'm migrating ultralite database from v.11 to v.16, and one of the problems we've faced on the past is the size growth of the databases. We've seen on migrating that size is lower that v.11, but we wanted to develop some kind of shrink mechanism to increase the perfomance of the application (windows mobile 6.5).

Is there any possibility to execute programmaticaly (on .Net application running on Windows Mobile) some kind of operation or code similar to the UNLOAD and LOAD applications for ultralite (ulunload and ulload) or DROP and CREATE database, bulk operations without breaking synchronization status on windows desktop environment?

The PASSTHROUGH Scripts could help? And the Agent Tasks? Our consolidated database (running on SQL Anywhere 16 under mobilink) has not any data on it (for Privacy laws reasons), so we can't recreate the database through synchronization from server.

Could you provide an example to do that?

Thanks a lot!

[chris_keating · Accepted solution]

There is nothing directly available to rebuild a database on device and in general, rebuilding on a device is not generally a good choice - even in modern devices with better memory and storage. In general, rebuilding a database will require space for the existing database and space for the rebuilt database plus any intermediary files. That means some multiple of the size of the source database. Rebuilding the database will decouple it from the sync environment because the rebuild will not retain sync progress state information. When rebuilding, you should successfully sync the database prior to starting the rebuild and make no further changes to the data.

You indicate that growth is an issue. When you rebuild the database on a desktop, what is the size differences that you see between the source and rebuilt database.

You also state that the consolidated is not storing data. Can you elaborate on what you are sync'ing and the scripts that are managing that sync?  Is the data sync'ing as upload, download, or by-directional? Have you marked the tables as nosync, sync, upload, or download to reflect how data is being sync'd for a given table. There is storage requirements for tables that are marked to be 'sync'd' so you will want to ensure that the sync type is defined correctly if database growth is an issue.

Database growth is generally expected but if the data is being sync'd and/or rows are being deleted, the rate of growth will eventually reach near zero in typical environments - I describe this as the 'working set size" - the point at which space requirements for new data can be found in free space that exists in the database. This might take time to reach depending on workload.

[Former Member · Accepted solution]

Hi Chris,

The database of the application grows and the perfomance is decreased, so that's why i need to recover free space (because on certain moment of business activity, a lot of data is deleted, and a lot of free space that database has to handle). I can determine a database size for establishing a significant perfomance loss, and if i can do a rebuild when the data is deleted then the size of database could be 10 times less sized.

With that, the application perfomance is greatly increased.

The data is bi-directional, and the data i'm syncyig is available in this post:

I think that the "working set size" is also affected by publications DDL, as explained last EBF 23041 Build #1501  - Engineering Case #736683:

  An UltraLite database could have unnecessarily grown by a small amount during
  each synchronization, or as a result of executing publication DDL. This has
  now been corrected.

Among other things that might increase database size (i guess). I'm Testing right now the new version and seems that with the same set of data the database size is decreased. I have not final results yet, but as soon as i get the results i will post it.

Anyway, i still think that should be a good idea to have a "shrink" method to be used on mobile applications to increase perfomance and reduce space, specially critical on old devices (windows mobile based).

Update: Size of database at it's Maximum critical Size or Working Set Size (v11) : 10Mb. Size after unload - load : 617Kb.