Memo table doubling in size

[dakotafox]

We are experiencing a problem with the Memo table for a table in our DBC. We are using VFP7.0 now but the problem had just begun while still in VFP6.
Each day the memo table for this particular table doubles in size each day. We have a nighttime process that is reindexing all tables (stonefield reindex) and the next morning the FPT table has doubled in size.
This FPT table had grown to 2GB in size so we copied the DBF table into two tables, half of the records in each table with the cdx files. The two halves now had a memo table of approx 35,000KB each which was about right because it had been 71,000KB for the complete FPT before increasing to 2GB. So then thru an alltrim replace process we rebuilt the memo fields in the original table and the FPT file was now back to 71,000KB. The next day after our reindex process at night, the FPT table is now 142,000KB with basic no change in data in the memo fields.

Any ideas on what is happening??

Thanks for your help.

 

[ramani]

HI

1) Removing of memo fields bloating...

** removes unused space, butdont drop deleted records
PACK MEMO

** remove deleted records and also pack memo
PACK

You need to open the database in Exclusive mode

2) However, a better starategy to avoid this...
a) Keep one file for permanent committed records which will not be edited, and another one for processing.
b) As and when the commiting in a pemanent way is done, remove the records from second one and put it in the first. This way, the Memo fields in first file will not bloat. The second file, being a transient one is easy to pack quickly.

Hope this helps you ramani :-9
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[DEDMOD]

Have you tried "PACK MEMO"? Or even just plain 'PACK'? It may well be that in the process of reindexing, records are duplicated and then deleted. If the resulting table doesn't have the deleted records removed by packing, they could grow forever.

Dave Dardinger

 

[jpstafford]

Pack the memo field to see if the memo field file *.FPT returns to some normal size.
Thoughts:
I beleive the memo field works like this and someone correct me if I'm wrong: The 10 digit memo field is only a pointer to the location of the data in the *.FPT file and this pointer is changed every time someone edits the MEMO field. The following in why the memo file will grow rapidly: When the memo field is edited, you only edit a copy of the original, then that copy is added to the FPT file, then the database pointer is changed pointing to the new edited data. This leaves data in the FPT file with no pointers to the database (orphans). These pointerless (orphans) can be removed by packing the memo field file or packing the entire database.
I am not sure why indexing increases the FPT file but try some troubleshooting first like packing the memo field file and you may come up with some conclusions.