Bloated Database

  • Thread starter Thread starter GeorgeMar
  • Start date Start date
G

GeorgeMar

Further to my previous discussion I have conducted a small
test to try to understand what is happening with my
database.

Sorry for being so longwinded but it is causing me a great
deal of pain. If someone could explain the results I
obtained,those of us who don't understand how the database
gets bloated will be eternally grateful.

I start with database A at 13,296 Mb. Database A is a
backend database and contains tables only. I export the
data from database A as text.

I copy the database into database B and delete the data
from all the tables one by one and compact it. Database B
is now 4,656 Mb.

I create a blank database C and import the tables
structure and relationship ONLY from database B. Database
C is now 1,128 Mb.

I copy database C into database D, delete the data in
database D and import the data from the text files
previously exported from database A. Database D is now
7,396 Mb

The questions are:
1. The difference from database B and C is 3,528 Mb.
What does that figure represent? Compacting does not
clean it up.

2. The difference between database D and A is 5,900 Mb.
They contain exactly the same data. What does the
difference represent?

3. What is the easiest way to clean up the database once
that kind of bloating starts ( when compacting is not
enough)?

many thanks from an appreciative programmer
george
 
Further to my previous discussion I have conducted a small
test to try to understand what is happening with my
database.

Sorry for being so longwinded but it is causing me a great
deal of pain. If someone could explain the results I
obtained,those of us who don't understand how the database
gets bloated will be eternally grateful.

I start with database A at 13,296 Mb. Database A is a
backend database and contains tables only. I export the
data from database A as text.
Click to expand...

Has A been compacted? Are you sure that it does not contain any queries,
forms, macros or modules?

I copy the database into database B and delete the data
from all the tables one by one and compact it. Database B
is now 4,656 Mb.
Click to expand...

You do a simple file copy of A to B?

I create a blank database C and import the tables
structure and relationship ONLY from database B. Database
C is now 1,128 Mb.
Click to expand...

Not sure why you emphasize ONLY. From what you have said, B only has
tables - nothing else - and you have deleted their data. What else >could<
you copy, other than table structures & relationships?

I copy database C into database D, delete the data in
database D ...
Click to expand...

From what you have said above, D can not >have< any data! How could you
delete it's data?

George, either I am misunderstanding you, or you have mis-decribed what you
are doing.

HTH,
TC
 
Thank you for your reply!

1. All the databases are compacted

2. I emphasise the word ONLY because that is the crux of
the matter.

Database B seemignly has no data. They have all been
deleted by me and compacted (4,656 Mb).

Datbase C starts off blank. (147 Kb)

If I import the tables' definitions and data (which is
default) from B, result: database C is 4,700 Kb

If I import the tables' definitions ONLY from B, result:
database C is 1,128 Mb

Here is the PUZZLE! Why the difference? I have tested
this many times with the same results.

many thanks
george
 
George, I'm still not clear what you're doing. Is this correct:

(1) Database B contains empty tables. Those table did originally contain
some data, but you manually deleted that data, and then compacted that
database.

(2) If you do an import of "table structure and data" from B into a new
database C1, C1 is 4700Kb. But if you do an import of "table structure ONLY"
from B into a second new database C2, C2 is only 1128Kb. Why the difference
between C1 and C2, given that the tables in B do not >have< any data?

Is that correct?

Are you creating two >seperate< new databases in step (2)?

Do you go through precisely the same sequence of steps to create each of
those databases, with the >single exception< that you make a different
selection in the "import what" box? (or whatever it's called)

How are you actually measuring the size of each database file?

Are you sure that the database file is closed (not open) when you take the
measurement?

HTH,
TC
 
TC I appreciate your help.

You are abosolutely correct on all counts. See answers
below.

George
-----Original Message-----
George, I'm still not clear what you're doing. Is this correct:

(1) Database B contains empty tables. Those table did originally contain
some data, but you manually deleted that data, and then compacted that
database.
(Correct)

(2) If you do an import of "table structure and data" from B into a new
database C1, C1 is 4700Kb. But if you do an import of "table structure ONLY"
from B into a second new database C2, C2 is only 1128Kb. Why the difference
between C1 and C2, given that the tables in B do not
have< any data?

Is that correct? (Yes)

Are you creating two >seperate< new databases in step (2)?
(Correct)

Do you go through precisely the same sequence of steps to create each of
those databases, with the >single exception< that you make a different
selection in the "import what" box? (or whatever it's called)

(Correct)


How are you actually measuring the size of each database
Click to expand...
file?

(By going into Explorer)
Are you sure that the database file is closed (not open) when you take the
measurement?
Click to expand...

(Closed and viewed through Explorer)
 
Ok. I hear what you're saying, but I don't see why it would happen. And I
have a fair understanding of what happens in MS Jet files "behind the
scenes" :-)

Could you please repeat the process (exactly as described below), then
compact C1, and compact C2, & give the pre- & post-compaction sizes of each?

When you check the sizes, are you closing the relevant folder & then
reopening it before you check? Don't get trapped by this situation:
- open folder;
- start db from folder;
- do something in the db which changes its size;
- close db;
- recheck the file size;

At that point the file size has not necessarily been updated in the folder
view. In my experience you need to close the folder & re-open it, to be sure
that the new size has been displayed. (I'm still on win98, mind you.)

HTH,
TC
 
TC Thank you again.

I am stopping there. I'll put it down to a corrupted
database. I am going to rebuild it and see what happens.

On another matter, whenever I import the tables into a
blank database, the importation puts an index on any field
with an ID in the name. Is there a way to stop that?

many thanks
george
 
Yes, you have to change your settings.

Select Tools: Options from the menu
Select the Tables/Queries tab
Delete the text in AutoIndex on Import/Create
Click the Ok button.

I believe that those settings are maintained across database files.
 
Thank TC and John

Deleting the AutoIndex works.

To complete the saga, I have rebuilt the database and I
have been able to reduce the database from 13Mb to 6Mb and
I no longer have the problem with compacting.

A couple of things that I have noticed. Firstly, when
rebuilding the database, import the database definitions
ONLY into a blank database. Then, build up the new
database by doing an SQL INSERT for each table from the
old (possibly corrupted) database into the new database,
to start with clean data.

Secondly, remove any unwanted Indexes. They can add Mb to
your database. (see earlier discussion re: index)

There is a phenomenon that I have noticed with some of my
databases (Back End and Front End) which is that some of
them will not compress when Zipped. In general, the
databases will compress from between 80% to 96% but not
them.

Any ideas?

Thanks again
George
 
Thank TC and John

Deleting the AutoIndex works.

To complete the saga, I have rebuilt the database and I
have been able to reduce the database from 13Mb to 6Mb and
I no longer have the problem with compacting.

A couple of things that I have noticed. Firstly, when
rebuilding the database, import the database definitions
ONLY into a blank database. Then, build up the new
database by doing an SQL INSERT for each table from the
old (possibly corrupted) database into the new database,
to start with clean data.

Secondly, remove any unwanted Indexes. They can add Mb to
your database. (see earlier discussion re: index)

There is a phenomenon that I have noticed with some of my
databases (Back End and Front End) which is that some of
them will not compress when Zipped. In general, the
databases will compress from between 80% to 96% but not
them.

Any ideas?

Thanks again
George
 
Back
Top