How do I make my database smaller

devicode · Site Admin Joined: 05 Mar 2005 Posts: 249

I have been using ResponseMailer for several months now. I do a database backup every month just in case anything goes wrong. However, my database has grown from 1 MB to 11 MB. I expect the database to grow but certainly not by that much. What can I do?

devicode · Site Admin Joined: 05 Mar 2005 Posts: 249

You will need to compact your database. From the menu choose Tools / Database. Then select Compact.
After the compact you database should be reduced from 11 MB to around 2 MB.

Overview
Running the Compact Database utility can improve the performance of the database. This utility makes a copy of the database file and, if it is fragmented, rearranges how the database file is stored on disk. When completed, the compacted database has reclaimed wasted space, and is usually smaller than the original. By compacting the database frequently, optimal performance of the database application is ensured, and page corruptions due to hardware problems, power failures/surges, and so on are resolved.

If a primary key exists in the table, compacting re-stores table records into their Primary Key order. This makes the read-ahead capabilities of the database engine much more efficient.

Compacting also updates the table statistics within the database that are used as optimizes queries. These statistics can become outdated as data is added, manipulated, and deleted from the various tables. Query speed will be enhanced significantly, because they are now working with data that has been rewritten to the tables in contiguous pages. Scanning sequential pages is much faster than scanning fragmented pages. Queries are forced to recompile/optimize after each database compaction.