ACC2000: Delete Queries Cause Size of Replicated Database to Grow

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.

Whenever you delete a record in a replicated Microsoft Access database, a corresponding record is created in the MSysTombstone 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 2000 Developer.

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 do this, use Microsoft Replication Manager to reduce the value of the RetentionPeriod property of the managed replica set.

CAUTION: Before you 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 RetentionPeriod 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 using the Microsoft Replication Manager:

1. Start Microsoft Replication Manager.
2. On the File Menu, click Managed Replicas.
3. In the Managed Replicas dialog box, click to select the replica set you want, and click Open. NOTE: Before you can update the RetentionPeriod 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.
   
   
4. On the View menu, click Properties.
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.

For more information about replica properties, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type replicas, properties in the Office Assistant or the Answer Wizard, and then click Search to view the topic.