Maintaining Tables

Table of Contents   Previous Page  Next Page    

As a database matures and records are added and deleted, they eventually need a little bit of maintenance.  When you log in as the administrator user you can run various table maintenance functions to keep your database running smoothly.

Note: A database preference controls how often the system will prompt you to maintain tables. Depending on usage, frequency of deletes, etc., maintenance should be performed every 30-90 days.

The following maintenance actions are available:

Analyze Table - Verifies indexes and keys for optimal internal operation for queries. It may be worthwhile running this action if large amounts of new data has been imported into the database.
Check Table - Checks the table or tables for errors.  This function might produce many rows of information for each checked table. The last row will have a Msg Type value of "status" and the Msg Text normally should be "OK". If you don't get "OK," or "Table is already up to date" you should normally run a repair of the table. A message reading, "Table is already up to date" means that there was no need to check the table.
Repair Table - Repairs a possibly corrupted table. Normally you should never have to run this command. However, if disaster strikes, the repair is very likely to get back all your data. Like the Check Table function, Repair Table may produce many rows of information for each checked table. The last row is the most important, and should be "OK".
Optimize Table - Should be used if you have deleted a large number of records.  Ordinarily, running an optimization once a month will keep tables compacted and well optimized.  The most common use of the Optimize function is to reclaim allocated but unused space in a table (such as when a large block of records has been deleted.)  This is a little like degragmenting a computers hard disk. You can see how much space a table has by running "Show Table Info" and checking the value in the Data Free column.
Show Table Info - Displays various statistics for a table, including the amount of allocated but unused space.  This information may be asked for by Support to help analyze an issue.  The following information is displayed:

- Table Name: the name of the table being acted upon
- Rows: the number of rows in the table
- Avg Row Length: the average number of bytes per row in the table
- Data Length: the total number of bytes of the table data file
- Index Length: the total number of bytes of the index data file
- Max Data Length: the maximum length of the data file
- Data Free: the number of bytes of allocated, but unused space in the table
- Create Time: the date/time the table was created
- Update Time: the date/time the table was last modified
- Check Time: the date/time of the last Check Table operation
- Engine: the storage engine used for the table
- Comment: the description of the table's use (or some information why the server couldn't access the table information if the table's information could not be displayed)

Maintenance Frequency

It is a good idea to get in the habit of running the table maintenance operations on a monthly to quarterly basis, depending upon how much activity you have on your database (i.e., a database with 10 concurrent users may require more frequent maintenance for optimal performance).  Think of table maintenance as basic housekeeping for a healthy database.  Like changing the oil in your automobile, it keeps the database engine running well.

At a minimum, run Check Tables and look at the results.  If any errors or warning are reported, run the Repair Tables option and then run Check Tables again.  When finished, run Analyze and/or Optimize Tables to refresh indexes and compact and organize your data.

Running Maintenance Functions

From within MemberTies...

1Login as the user "mtadmin".
2Choose Tools > Database Admin Tools > Maintain Tables.
3Continue to Step 4 below.

From the Server Utility...

1Start the server utility and click Maintain Tables.
2Choose the desired database, and enter the mtadmin username and password.
3Continue to Step 4 below.


4Select one or more tables to affect in the Tables area. (If you're not sure, just right-click the list and choose Select All)
5Choose the action to perform from the dropdown, then click "Start".
Normal Housekeeping can be accomplished by selecting all tables, and then successively running Check, Repair, and Optimize.
6Information about the operation will be displayed in the Details section.
7When the operation is finished, you can write the contents of the "Details" section to a log file if you wish. To do this, click "Write Log", enter a filename in the "Save Message Log As" dialog, and click Save.
8If you want to perform another operation, repeat the previous steps, otherwise, click Close.

Note: If for some reason you cannot start MemberTies to run table maintenance, the same functions can be run from the Server Utility.