BE size growing very fast

[Newman]

Hi!
I have a database that, when compacted, is about 20M BE & 20M FE.
Starting a couple of weeks ago, the BE grows very fast.
It can go as high as 300M in a single day.
When I do compact it, it reduces to 20M, with no problem.
The problem is that sometimes I get a message saying that there is no more space in that directory.
I wonder if that could be because of the MSJetversion.
The office where I work has thousands of computers, and the administrator doesn't want to install the service pack on them.
If that is not where the problem lies... please tell me where it is!
Thank you!

 

[Newman]

I do use a delete query and a add query... could that be the problem?
I don't understand how these queries, could make my db grow that much.
Thanks!
PS. You have a thread for your birthday in the watercooler. Just in case you didn't know about it, since I know that you don't hang around watercooler much.

 

[RichMorrison]

Newman,
<<
I do use a delete query and a add query... could that be the problem?
>>

Yes. When you delete, the space that was filled by the deleted records is not "recovered".

You must compact the BE to recover the free space.

RichM

 

[Newman]

What can I do to make the db grow less then?
I need those queries, but the space left for my db on the server can't be more than 300M.
I do compact it every night using a vbscript with the task manager but, if the db is very close to 300M, it won't compact because it has no space to even create the ldb file.

 

[RichMorrison]

Newman,
<<
What can I do to make the db grow less then?
>>

Can you create multiple BEs and distribute tables between them? That way each BE will be smaller and easier to compact.

RichM

 

[Newman]

If the problem is caused by the queries, I don't see how using multiple BE would let me compact them.
The BE with the table that grows would not compact anyway.
Am I wrong?

 

[RichMorrison]

Newman
<<
If the problem is caused by the queries, I don't see how using multiple BE would let me compact them.
The BE with the table that grows would not compact anyway.
>>

In a previous post, you said there is a problem compacting a BE because it was too big.

I am *assuming* that you have 2 or more tables in the BE. If so, then create one BE for each table. You will have more total BE datasets, but each will be smaller that the one that holds all the tables. Therefore, you should have fewer problems compacting each BE.

N'est pas ?

RichM

 

[Newman]

(Those frenchmen... they never understand at the first time)
Let say I had a 300M BE in a 300M directory.
Now, I make another BE with the table that grows to much.
I now have a 15M BE and a 285M BE. (The 285M being only one table... the one that grows to much.)
It still totals 300M in a 300M directory.
Wouldn't it still be impossible to compact the table that grows to much?
Thank you for your help!

 

[Newman]

What I've done, so far, is create a copy on my local drive of the db. Then compacting it from there, and then replace the one on the server by the compacted one.
BUT, I am still working to find a way to compact it directly on the server.
Also, I beleive that compacting it might just be like putting a bandage on the wound, it doesn't heal the problem, which is a table getting from a couple of megs to 300M in only 8 hours.

 

[Vassago]

You must have a lot of queries running daily in order to make the size of the database increase that much! Maybe you should try a different method of accomplishing what you want to accomplish than using delete queries. Just a thought.

Also, when you make two BE databases as opposed to one, the ending databases total sizes together are usually smalller than one large BE database. I've done this on many occasions and is should do the same for you, therefore, RichMorrison's suggestion should work wonders for you.

HTH,

Vassago

 

[Newman]

Well... I'll give it a try. Don't know yet what I am going to do as a different method, but will find out... Have to!
Thank you all!

PS: Life is like a box of chocolates, the first one you get your teeth in is always the one you dislike.

 

[RichMorrison]

Newman,
<<
I now have a 15M BE and a 285M BE. (The 285M being only one table... the one that grows to much.)
>>

Mon Dieu ! (That's the other phrase I remember from high school)

I think you need to find a way to redesign the app so one table is not 285M.

Without knowing what the users are doing, I can't offer any specific suggestions.

What do they do that causes a lot of adds and deletes ?

RichM

 

[Newman]

YES! Got it!
I have a query that makes an archive of the data of an oracle linked table when a command button is pressed.
The problem is that they changed the table in oracle so that it now has all the works done since 1980 instead of the works done in the past 2 weeks.
So, when someone use the button, it brings all their oracle table to my access one.
I added the criteria: TheDate > (Date()-15).
Now it works fine!
Thank you for your help!