Back end database bloat

[vicsaccess]

I'm seeing random bloating of my backend file. will be fine for days and then like this morning i looked at it and it went from 8500 kg to 12,000 kg overnight. my thought is that its problably a datatype wrong or something of that nature but is there a way to see where the bloating is, to narrow it down to a table or field?

[orange]

That's a pretty "heavy database". I think you meant kb.
Do you save photos or large text or other objects in your BE database?
Do you do lots of adds and deletes?
Do you do a regular compact and repair?
Do you do regular backups?
Do you have a lot of temporary tables that get filled, emptied, reused??

What datatype do you think may be an issue?
What activities happened between the 8500 and 12000 sizes?

[Micron]

Did you compact and do a comparison? The info would help to know if it's just a matter of overnight data entry. Is the db run at night by any updating process? There are several reasons why bloat happens, but which might apply depends on factors not revealed here. Attachment fields? Image fields? Design changes are obviously out of consideration, but over-writing tables is not so obvious to us. If nothing is happening overnight, suspect incorrect reporting of the file attributes by Windows. Rather than rely on Windows Explorer, open the db and get the file attributes from within. Consider a compact operation on close if the db reaches a certain size only, which you determine. Something that won't occur every time. I think your example is just under 4Mb which is not bad - unless maybe that represents a huge percentage of its original size and/or is happening every night.

[vicsaccess]

Thanks guys, yes you caught my typo, it is KB and not KG. i don't save any photo's or large text. The operators do have the option of deleting entries but usually that only happens once or twice a shift per location. I'm having a hard time doing regular compacts and repairs mainly because this file is used 24 hours a day, some locations only run day and evening, some run evening and graveyard. I try and get on it every weekend and make any changes along with my compact and repair operations which i just did Sunday. Most of the time due to this and the potential for corruption I make a copy each day and archive it. There are no temporary tables. Data types are pretty simple, number, date/time, yes/no, short text and a few long text just for comment fields. The only thing i see different yesterday was that one of the shifts last night had quiet a few errors logged due to not being able to save records, it was two errors reoccurring, data type conversion and type mismatch, both I'm pretty sure i know the cause. I did run the performance analyzer and it made a few suggestions, one was adding an index and three was changing from short text to long. i did make my backup copy and compact it which dropped it down to its normal 8500kb.

[Micron]

Recurring errors for type mismatch or conversion failures can contribute to bloat. 24 hr continous use with never a moment between users? If you had a table of logged on users you could test the count during any fe shutdown and compact be if > set size.

[orange]

..........a few errors logged due to not being able to save records,....

Have you identified the cause and resolved the issue?

[vicsaccess]

Thanks, i am pretty sure i know the reason for the errors but wasn't sure if they were the culprit of my bloat. there is time theoretically between like 3am and 6 am but the locations don't always keep me up to speed when production is running late, I've sat up late a few nights thinking their shift was about done and come to find out they had mechanical problems earlier in the day that made them run late and didn't finish till after i had given up
I hadn't thought of using my log table to help, since i can't compact while other users are in the BE, could i instead set up the FE's to check the user log or even use the UserRoster and if <=1 then compact the BE? actually not sure if that would be possible or not, do you know of any examples of having the last users FE compact the BE?