Access file size limit for 2003 and 2007 and memory issue

[Grasshopper]

(1) Can someone tell me the max file size of Access file?

(2) Also, when I open a 2GB Access file, does it uses 2 GB of memory?
The reason I asked is that when I opened such file, I see the
available memory in the Tasks Manager window went down to zero
gradually.

(3) I saw some posts that suggested using table link to deal with the
size issue, but would the performance (speed) go down when running
queries using tables from two different Access file?

- Grasshopper -

 

[Arvin Meyer [MVP]]

(1) Can someone tell me the max file size of Access file?

2 GB

(2) Also, when I open a 2GB Access file, does it uses 2 GB of memory?
The reason I asked is that when I opened such file, I see the
available memory in the Tasks Manager window went down to zero
gradually.

It will eventually use at least 2 GB of memory, but not necessarily RAM.
Windows allocates memory from a pagefile (usually equal to 1.5 times the RAM
on the machine)

(3) I saw some posts that suggested using table link to deal with the
size issue, but would the performance (speed) go down when running
queries using tables from two different Access file?

I've never noticed a performance hit. The biggest database that I've ever
seen was a 30 GB file in Access 2.0 which had a 1 GB limit. There were about
78 linked tables from different databases all linked to a single front-end.
The tables contained OLE fields with images in them. It had good
performance.

 

[Grasshopper]

Thank you so much Arvin. Your answer to Q3 was particularly useful.

Do you know if i run quries by linking 4 tables (and assuming each
table has exactly 1 GB of data), what it the maximum memory that it
will use during the run time?

Also, when I designed the database, the size issue was not there, so
the design of the table was not very efficient. For example, a max of
2 characters field would take 50 characters space (as by the
default). Given that I have two table with 4 millions records, this
should "eat up" a lot of space. Is there a way to calculate the size
of the database based on the field size allocated and the number of
records?

- Grasshopper -

 

[John W. Vinson]

Also, when I designed the database, the size issue was not there, so
the design of the table was not very efficient. For example, a max of
2 characters field would take 50 characters space (as by the
default).

Ummm... No. Access does NOT store trailing blanks. A Text field is really a
"varchar" - if there are two bytes used, that's all that's stored on disk. The
50 bytes is an upper limit.

Given that I have two table with 4 millions records, this
should "eat up" a lot of space. Is there a way to calculate the size
of the database based on the field size allocated and the number of
records?

Not easily, given that trailing blanks aren't stored; you need to factor in
some overhead for each record, system tables, the size of indexes, etc. etc.

Your concern about memory is probably not relevant, actually. Windows memory
management is pretty sophisticated; you will certainly have a paging file,
which will be used as an extension to installed RAM. A 512Mbyte machine can
easily run a program using over a gig of "memory" by swapping memory out to
the page file - it will, of course, be slower as a result, but it *will* run.

John W. Vinson [MVP]

 

[Grasshopper]

Hi John,

Thank you for your answer. Yes, you are correct on the trailing
blanks. Before I read your reply I changed the field size on the
table and compacted it, the file size stays the same.

Regarding the memory issue, I opened a 2GB file and the 2BG memory got
consumed slowly until the available physical memory become zero.
Despite that there are still cache 1GB of memory, the query cannot be
ran any more.

- Grasshopper -

 

[Grasshopper]

By the way, if anyone know the answer to the folloing question, please
tell me. Thanks.

====================================================
Do you know if i run quries by linking 4 tables (and assuming each
table has exactly 1 GB of data), what it the maximum memory that it
will use during the run time?
====================================================

- Grasshopper -

 

[Allen Browne]

Grasshopper, the algorithms Access uses are proprietry (not published.)

Even if they were published, there would not be a simple answer, since it
depends on your version of Access (JET), your version of Windows, and your
configuration (swapfiles, location of data, Unicode compression and language
settings, etc.) It is not even practical to determine the physical size of a
table.

If you really have a 2GB database, get your information out of Access and
into another database quick before you lose it. You won't be able to
red-line for very long before it blows up.

 

[Grasshopper]

Thank you Allen. I will be careful with the file. Last time, I tried
to deleted two 2GB files on my computer and unexpectedly (and still
unknown) instead of these two got deleted, the whole folder got
deleted. It cannot be undo and the folder cannot be found in the
recycle bin. This has nothing to do with the fact that they are Access
files, but probably merely on memory issue (I do not know for sure).
My laptop has 1.5GB of memory.

Now my querstion is, if any body know. Can a file with file size
larger than the REM on the PC be deleted and then restored?

I know this has nothing to do with Access, but it is a spin off
question.

- Grasshopper -

 

[dbahooker]

Arvin

you've never noticed a performance hit from partitioning data into
multiple databases?

I've had awful performance / stability with 25mb of data

anything even CLOSE to this size should be an Access Data Project
2007-- which has a 4gb limit in SQL 2005 Express

 

[David W. Fenton]

Regarding the memory issue, I opened a 2GB file and the 2BG memory
got consumed slowly until the available physical memory become
zero. Despite that there are still cache 1GB of memory, the query
cannot be ran any more.

That's probably caused by an Access error condition. The whole data
file should not be loaded into RAM, because that simply isn't the
way Access works. It only loads what is needed, as needed. Since you
can't look at 2GBs of data onscreen (nor process it all at once),
there would be no utility in loading the whole thing.

 

[David W. Fenton]

By the way, if anyone know the answer to the folloing question,
please tell me. Thanks.

====================================================
Do you know if i run quries by linking 4 tables (and assuming each
table has exactly 1 GB of data), what it the maximum memory that
it will use during the run time?
====================================================

The question can't possibly be answered because it all depends on
the details of indexing and the amount of RAM and the size of swap
file and what other programs are running, and which operations your
query is performing.

 

[David W. Fenton]

Can a file with file size
larger than the REM on the PC be deleted and then restored?

Deleting and restoring a file has nothing to do with RAM. All that
happens when you delete a file is that the index of files is updated
to say that the file is deleted. When you restore it, you are just
setting that flag back. No significant RAM is involved.

There is not even significant RAM involved in copying from one disk
to another, as it's done in pages of data.

 

[Grasshopper]

Thank you for your input, David.

From my experence, when I open up a 2GB Access file, the system is

using up 2GB (or somthing close) of memory even though it is accessing
all the tables at once. I think since Access is not a server, the
system needs to load everything into the memory for it to be opened.
What do you think?

- Grasshopper -

 

[David W. Fenton]

Thank you for your input, David.
using up 2GB (or somthing close) of memory even though it is
accessing all the tables at once. I think since Access is not a
server, the system needs to load everything into the memory for it
to be opened. What do you think?

I already said what I think. Your file is corrupt in some way that
causes a memory overflow.

Opening a 200MB MDB file does not increase RAM usage by 200MBs. That
just isn't the way Access works. When you open it, all that has been
read are the headers of the file, which are probably much less than
1MB in size.

 

[dbahooker]

Access Data Projects do not have a limit on size

if you're hitting the limits of MDB I reccomend moving to ADP.

because it's always impossible to forecast how an app will be used for
the next decade; I just don't think that using MDB makes a whole lot
of sense

-Aaron

 

[Grasshopper]

Thank you, David. You are right, the Access file is probably
currupted.

Aaron, what is ADP? Sounds like the payroll company.

- Grasshopper -

 

[dbahooker]

ADP = Access Data Project
you can upsize your Access MDB application to a SQL Server client-
server application.

for me-- It's a lot more efficient to build an app and let 'best of
breed' enterprise-level apps like 'SQL Server Developers Edition (it's
only $49) you can use 'Index Tuning Wizard' or 'Database Tuning
Advisor' to fix a lot of indexing problems.

I just think that MDB is not reliable enough for real-world use

Hope that helps

-Aaron

 

[Grasshopper]

Hi Aaron,

I also have a SQL Server Developer Edition. Do you know the major
limitation between a Enterprise version of the developer version?
By the way, where did you get your Developer Edition for $49?

- Grasshopper -

 

[Arvin Meyer [MVP]]

The only time that Access reads the entire file is if there are no indices
and Access must do full table scans. Even then it only reads the tables it
needs.

 

[Arvin Meyer [MVP]]

Unfortunately, Aaron can't be relied upon for consistent or accurate
information. He believes that using ADPs are the only use of Access that is
acceptable. Clearly, he is at odds with every other developer and with
Microsoft itself. Most of us just ignore him and let him rant. A few attempt
to argue with him, but that like arguing with a fencepost.