How to determine which table uses the most space in an Access database

tags:

ms-access

views:

323

answers:

+2 Q:

How to determine which table uses the most space in an Access database

Hi,

is there any easy way to determine how much space each table in an Access 2007 database is using?

I have an unusually big Access database and need to find out which table uses the most space. The row counts do not give enough information about the used space.

Regards

/Frank

For functioning Access databases you can get the simple tool Access Memory Reporter 1.0 which shows the amount of memory the tables and indices needs. Note that I haven't tried this tool myself.

What is your objective once you discover the largest table? How large is your MDB? Have you compacted it recently?

How much does it shrink when you compact it? That is are you creating and deleting a lot of tables/records in it? If so see the TempTables.MDB page at my website which illustrates how to use a temporary MDB in your app.

Are you using a lot of graphics in the tables?

Tony Toews 2009-10-07 20:32:35

Thanks for pointing out that tool. Unfortunately, the tool tells me only that most of the space is occupied by "Memo/OLE" data, but it does not tell me which table contains how much "Memo/OLE" data. Instead, all "Memo/OLE" data is grouped under a header "## not directly related to one table ##".Compressing the database does not help; the MDB file does not shrink at all.- Frank

fmunkert 2009-10-08 08:40:45

As a workaround, I now have imported the MDB database to an SQL Server, and in SQL Server Management Studio, I have added the column "Data Space Used" to the list view. This tells me which table uses how much space. Using that procedure, I found out that a single table in my database almost uses all of the disk space.

fmunkert 2009-10-08 09:04:16

Memo/OLE data is not actually stored in the record. Instead there is a pointer to a page where each Memo/OLE is actually stored. So it's not actually with the record. However there is some VBA code that you could run to determine which tables have Memo/OLE fields and how many of them there are. Hmm, and possibly the size of each but I'd have to poke about a bit on that. Although you now appear to have figured out which table is taking up the space.

Tony Toews 2009-10-08 20:30:40

+1 A:

This is actually an interesting problem because Access uses variable length records for storing it's data.

The best way to do this accurately would be to go through every record and every field of the table and add up the length of the fields. It could take a while if the tables are large. It wouldn't pick up the size due to indexes and relationships.

In our Total Access Analyzer program, we have a few reports that provide an estimate of the table size using a simple record size estimate times the number of records. An example is shown here: http://fmsinc.com/MicrosoftAccess/Documentation/Reports/Table%5FSizeBySize.html

That might be sufficient for ballpark estimates or relative size comparisons.

Another, and probably very accurate way to measure this would be to create a new database and export the table into it. Compact the database and subtract the blank database size from it to get the table's size.

Luke Chung 2009-10-10 00:08:18

+ 1 for the "create a new database and export the table" suggestion rather than the shameless plug ;)

onedaywhen 2009-10-12 09:14:20

ansaurus

tags:

views:

answers:

How to determine which table uses the most space in an Access database

related questions