ACC: Delete Queries Cause Size of Replicated Database to Grow

• Microsoft Access versions 7.0, 97
• Microsoft Office 97 Developer Edition
• Microsoft Access Developer's Toolkit, version 7.0

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you run multiple delete queries in a replicated Microsoft Access database, the size of the database may increase significantly. Compacting the database does not reduce its size.

CAUSE

Whenever you delete a record in a replicated Microsoft Access database, a corresponding record is created in the MSysTombstone system table. MSysTombstone is a read-only system table in which the Microsoft Jet Database engine stores information about deleted records. This information remains in the MSysTombstone table until the retention period has expired and the database is compacted. If you use the Microsoft Access user interface to replicate a database, the retention period of the resulting replica set is set to 1000 days. However, if you use Microsoft Replication Manager to replicate a database, the default retention period is 60 days.

NOTE: Microsoft Replication Manager is installed with the Microsoft Office 97 Developer Edition or with the Microsoft Access Developer's Toolkit version 7.0.

RESOLUTION

You cannot prevent Microsoft Access from storing a reference to a deleted record in the MSysTombstone table. However, you can reduce the number of days that a reference to a deleted record remains in the MSysTombstone table. To accomplish this, use Microsoft Replication Manager to reduce the value of the KeepDesignChangeHistory property of the managed replica set.

NOTE: When you use Microsoft Replication Manager to reduce the length of the retention period, it is important that you remember to synchronize each database in the replica set before the retention period expires. The KeepDesignChangeHistory property affects how long a record of design changes is retained. If the retention period expires before a particular database has been fully synchronized, you will be unable to synchronize that replica, and it will no longer be a part of the replica set.

Follow these steps to reduce the length of the retention period of a replica set:

1. Start Microsoft Replication Manager.

2. On the File Menu, click Managed Replicas.

3. In the Managed Replicas dialog box, click to select the desired replica set, and click Open.

   NOTE: Before you can update the KeepDesignChangeHistory property, you must first ensure that you are managing the Design Master of your replica set. If your Design Master does not appear in the list, click Manage New in the Managed Replicas dialog box.

   NOTE: If you are using Microsoft Replication Manager version 3.0, which is installed with the Microsoft Access Developer's Toolkit version 7.0, you must first select the managed folder from the Folders drop-down combo box on the toolbar, and then select the Design Master from the Replicas drop-down combo box on the toolbar.

4. On the View menu, click Properties.

   NOTE: If you are using Microsoft Replication Manager version 3.0, which is installed with the Microsoft Access Developer's Toolkit version 7.0, click Replica Properties on the View menu.

5. In the Properties dialog box, click the Replica Set tab.

6. Reduce the number of days in the Keep Design Change History box to a value smaller than 1000.

STATUS

This behavior is by design.

REFERENCES

For more information about replica properties, search the Microsoft Replication Manager Help Index for "replicas, properties" and then display the topic "Viewing and Setting Replica Properties."