Very big database growth

[Gkirkup]

I have an application with about 30 remote users. The back end data size is about 500mb after Compact & Repair. However, we are often seeing it grow to over 2GB in a day! We can compact and repair at the end of the day, but we see corruption before then. Possibly caused by the database exceeding 2GB?
We never saw this with 2003 but it happens almost daily with 2010. We do have some functions that delete several thousand records and replace them, but I don't see this causing a 200% growth in database size.
Does anyone have any ideas what is happening?

Robert

 

[Galaxiom]

Deleting records and replacing them will bloat any database.

Could the functions be changed to update the records instead of replaing them?

Otherwise can you do this work in another database and then insert them in the real backend?

 

[Christian1977]

Hi Gkirkup,

if you fix your problem with a compact db function, with an external program you can close and re-open your db.
Remember to insert the flag in access on the auto-compact db function.

 

[jdraw]

Gkirkup,

Have you solved this? There's been no activity for > 12 days...

 

[RainLover]

Consider splitting the Back End.

Put the offending Tables in one and the other tables in the other database.

You will still have to do compact and repair.

You may have to consider a different Back End unless you can find a solution to this problem. (I assume 2007 is still limited to 2 Gb)

I would suggest MySQL. It is powerful and will handle your size with ease. It can also be used on the internet as just about every host site makes MYSQL available.

It is also free and much easier to use than SQL Server.

Suggest you discuss here what you are doing with this delete and repopulate. There may be a better way of doing things.

One solution may be to delete the split DB and create a new one on every import.