The data maintenance script performs multiple cleanup procedures on your database in order to reduce its size and improve its performance. Three of the most significant cleanup tasks in the script are the following:
- Purging Synchronization Transaction (TPS) Records
- Purging Activities
- Other Data Maintenance
Purging Synchronization Transaction (TPS) Records
TPS records are generated each time a firm contact is changed. These records are then used by the User to Firm Contact synchronization process to determine what changes need to be applied to user contact records. If these records are not purged your database can grow very large, impacting User to Firm Contact synchronization performance.
This script deletes the TPS records in the database that are no longer needed. Whether a TPS record is needed or not is determined by the age of the record.
Purging Activities
After a period of time, activities are no longer useful contact data. For example, an activity created when a contact changes phone numbers may be useful for a few weeks, but once users have become aware of the change, the activity is no longer needed. To prevent your system from becoming cluttered with these outdated activities, you can set expiration dates for activities.
When you create or edit an activity type, you can set a number of days the activity exists in InterAction before it expires. For example, the activity type “Phone Number Change” is set to expire after 14 days. Any activity created using that type expires 14 days after it is created.
For more information on setting the expiration dates of activity types, see the Configuring InterAction guide.
Other Data Maintenance
The database maintenance stored procedure performs a number of cleanup/maintenance routines on the data.
One of the types of tasks that the database maintenance procedure performs is to physically delete data after a certain period of time that has been logically deleted from the system. It does this on both the INT_AUX_CONTACT and INT_AUX_PROJECT tables. It also physically deletes old audit data from the INT_AUX_AUDIT table, and old log data from the INT_AUX_CONTACT_SYNC_LOG, INT_SCHEDULED_PROCESS_LOG, INT_CONTINUOUS_PROCESS_LOG, and INT_DATA_LOG tables. The routine clears records from the table, which controls the syncing of data from InterAction with a user's contacts in Outlook. The routine also removes expired activity records (INT_AUX_ACTIVITY) from the system, as well as completed tasks (INT_TASK). Finally, it removes records added via IAObjects that have never been hooked to another record in the system.
Another type of task that the stored procedure performs is that it cleans up potentially out of date data. It validates the IDs of company records tied to people records in INT_AUX_LISTING. It also validates the user and listing relationships on INT_USER. It checks the user IDs for additional information fields of type User for both folders and listings. It sets the client code additional information fields if they are not set.
A third type of task that the stored procedure performs is to clean up orphaned data. Some of the tables that it cleans from are INT_AUX_DIR_LIST, INT_AUX_ADDRESS, INT_AUX_MAILING_ADDR, INT_AUX_LST_ADDR, INT_AUX_ELEC_ADDR, INT_AUX_LST_EADDR, INT_AUX_PHONE, INT_AUX_LST_PHONE, and INT_AUX_LIST_LIST.
Run Data Maintenance Script
You must schedule the InterAction Process Manager to run the data maintenance script. In general, you should schedule this process to run after hours or when there are not many users on the system. It is highly recommended that you schedule an on-going process to run the data maintenance script at a minimum of once a week and at a maximum of once a day.
- Log on to InterAction Administrator.
- Following the instructions in Scheduling Processes, schedule a process for running the data maintenance script. When scheduling the process, specify the following:
- Select the Run Database Script process.
- Choose the Edit button in the Process Settings section and select the script file: intia_data_maint.sql.
- When finished configuring the process, choose OK.