need help reducing table/file size

[Guest]

I've got a big, messy database, and it's already at the 2GB limit and I'm
trying to find ways to pare it down.

I already ran a compact, and it didn't help (at least not nearly enough).

I need a way to determine where all the bloat is coming from. I know how
many records are in each table, but not how much size that represents in
terms of the file size. There's only so much I can do in terms of removing or
paring tables. (I'm just a lowly contractor who has to work with what I'm
given - I can't rewrite/redesign the database as much as I'd like to)

Basic stats: about 80 tables with about 12,000 records in each table, then
another 20 with anywhere from 5,000 to 10,000 records, and 3 enormous tables
with between 450,000 and 500,000 records. It's got maybe 75 queries, but no
forms, reports, macros, or modules. (no code to bloat things)

I've moved a bunch of those 12,000 record tables to an archive db, but it
didn't change the file size nearly as much as I thought it would. Is there a
way to find out where all the bytes are hiding? That way I'd know where to
focus my time in trying to reduce the size.

Thanks!

 

[Guest]

I don't see where you would have 2G of data, based on those table sizes. I
would look for pics or other objects being stored.

Sharkbyte

 

[Guest]

I figured it out, so I thought I'd post my "solution" in case it might help
someone else.

I created a new blank database, then imported tables one by one, watching
the file size change with each table. It took an hour, and it was a pain in
the rear, but it got the job done. I discovered 20 tables that were taking up
50MB EACH! There's half the database size right there! They only had 12,000
records in them, but 126 fields!

No pictures or other objects, just a heck of a lot of data hiding in some
rarely used tables. At least now I know where to start cutting!

Thanks for the reply, Sharkbyte!