Defragment and rebuild My Money database

From MTHWiki

Jump to: navigation, search

My Money 2.0 Manual | Back up your Money file<<Previous

Defragmenting My Money database

My Money database can get fragmented after large amounts of data are inserted or updated. This happens because, by default, My Money does not return unused space to the operating system even if the space is no longer needed. However, My Money does provide a way to reclaim unused space, defragment the data and rebuild associated indexes.

To defragment and rebuild the database please use File-Data Maintenance-Defragment and Rebuild Database menu.


Defragmentation is an slow procedure; it is guaranteed to recover the maximum amount of free space, at the cost of temporarily creating new tables and indexes before the changes are is committed. In addition, My Money will compress the internal tables and indexes whenever possible when defragmenting.

Checking database consistency

If you experience hardware or operating system failure, you can use the "Check database consistency" function to verify that the database is still consistent.

Check consistency only if there are indications that such a check is needed because a consistency check can take a long time on a large database.

  • Base tables are internally consistent
  • Base tables and all associated indexes contain the same number of rows
  • The values and row locations in each index match those of the base table
  • All indexes are internally consistent

If database is found inconsistent the following error messages are generated:

If the row counts of the base table and an index differ, error message X0Y55 is issued:

ERROR X0Y55: The number of rows in the base table does not match 
the number of rows in at least 1 of the indexes on the table. Index 
'T1_I' on table 'APP.T1' has 4 rows, but the base table has 5 rows. 

If the index refers to a row that does not exist in the base table, error message X0X62 is issued:

ERROR X0X62: Inconsistency found between table 'APP.T1' and index
'T1_I'. Error when trying to retrieve row location '(1,6)' from the 
table. The full index key,including the row location, is '{ 1, (1,6) }'.

If a key column value differs between the base table and the index, error message X0X61 is issued:

ERROR X0X61: The values for column 'C10' in index 'T1_C10' and 
table 'APP.T1' do not match for row location (1,7). The value in the 
index is '2 2   ', while the value in the base table is 'NULL'. The full
index key, including the row location, is '{ 2 2       , (1,7) }'.

The suggested corrective action is to defragment the database and recreate the indexes.

Freezing/Unfreezing Database

If you need to bring My Money database into a state in which it can be safely copied you can use "Freeze Database" command to ensures that all data and the logs are written to disk. Once database is frozen,My Money will block all modifications to the database until "Unfreeze" command was executed. This functionality could be used for database replication over the network, online backups, etc.

My Money 2.0 Manual | Back up your Money file<<Previous

Personal tools