Regardless of the type of database (MS Access, MSSQL, MySQL), it tends to grow in size. There are two types of records, which influence database growth - temporary and permanent records.
Permanent – relate to the user accounts and ads posted by clients.
Temporary – relate to statistics logs.
Impact of Permanent records in database for the most part cannot be controlled. More people visit your site – more accounts and ads created. Faster the database would grow in size. The only way to control amount of posted ads is to delete expired ads after certain date.
Temporary records on the other hand could and must be controlled to prevent fast database grow.
Below some recommendations, which would help to control excessive database grow and keep database neat and healthy.
Recommendations
- Monitor
Expired ads. Justify and remove expired ads, which are older than certain date
and not likely be reposted by clients.
- Configure
Events Logs to record only a few essential types of events.
- At
least once a week login to admin section and go to Visits Log and Events log
pages. This would automatically remove older records from db.
Visits log – Records older than 30 days removed automatically (you must visit the visits log page for this to occur).
Events Log - Records older than 90 days removed automatically (you must visit the events log page for this to occur).
If traffic to the site is substantial, then occasionally clean logs completely. If necessary, you may download statistics in CSV format before clearing logs.
- Monitor
Replies to ads. Clients may delete replies to ads, in which case replies still
stay in database but visible to admin only. Replies in this case flagged as "Remove".
You may safely delete replies while "REMOVE" Flagged Only checked.
You may also delete all replies (with "REMOVE" Flagged Only unchecked)
which are older than certain date.
- Another
way to reduce database size is to clean banners rotator statistics logs. Statistics
log maintained for each banner individually. For active banners, which stay in rotator for a long time and for which
view statistics in [on], do the Reset and then Activate banners
over. Reset clears banner statistics in database, which might be extensive
depending on a time banner stays active.
When you have 2 or more zones on a single page (specifically default page, which sustains most hits), then UPDATE database statements executed for each banner displayed at the page to record View statistics. This in turn has a great impact on database (SQL specifically), where SQL transaction log grows extensively, and in some cases might ever be larger than the database itself (comments with site traffic). Therefore, for banners, which do not require view statistics, it is strongly recommended to set Gather View Statistics [off].
Special care for MS Access database
Records in database constantly added and deleted. Some of the records deleted automatically and some deleted manually. The most automated routine at classified is Sessions maintenance, where new sessions are added to database with each new visitor and older sessions removed. While MSSQL and MySQL database reallocate space occupied by deleted records, MS Access database does not. In time, MS Access database (cldb.mdb file in db folder) might grow enormously and take up large amount of space on a disk. This would also affect the site performance. Even if you constantly clear logs, the size of the MS Access database would NOT be reduces.
The only way to reallocate deleted records space in cldb.mdb is to use Compact Database in Global Manager. Do Compact MS Access database at least once a month or after removing a large amount of records by clearing logs for instance. Compacting not only reduces the size of database, but also might repair some internal inconsistencies in database. While using Compact Database, a backup of the database always created in db/backup folder.