Not enough space on temporary disk, MaxLocksPerFile, MaxBufferSize

[Philippe]

Hi,

I have a problem with error mesage 'Not enough space on temporary disk'

I want to edit a field in a table, that is a source in relations with 5
other tables. Editing this field changes its value in about 7 millions
records included in the 5 dependant tables.

With MaxLocksPerFile = 9500 (default value), I get an error:

'Too many locks. Increase the MaxLocksPerFile parameter.'

Until there, nothing wrong. I set this parameter to a high value (e.g. 10
000 000) in the registry, as indicated in the Microsoft knowledge base.

The edition goes further than before, but then I get the error:

'Not enough space on temporary disk'

When I do the same operation by program, I get the same problem:

DoCmd.RunSQL " UPDATE Field FROM Table SET Field='X' ", false
gives the error 'Not enough space on temporary disk'

CurrentDb.Execute " UPDATE Field FROM Table SET Field='X' ", false
gives the error 'System resources exceeded.

I can't figure why I get this error. Microsoft tells in the knowledge base
that there was a limit of about 268 Mo on the temporary files Jet????.Tmp in
Access 95, but that since Access 97, this problem has been solved.

I've monitored the Jet????.Tmp file that is created during the edit, and I
get the error when the file reaches a size of about 220 Mo. And I have about
24 Go of free space on my disk used for temporary files (C:\Windows\Temp).

I thought maybe that the problem had to do with the system memory, and not
with the system disk space, so I went to investigate the MaxBuffersize
parameter of Jet, and I tried to monitor the memory use.

With MaxBufferSize set to 0 (default), the memory use increases slowly to 30
Mo before giving the error. Its is rather stange, my PC has 1 Go RAM
installed. Why a value so low ? According to the automatic setting given in
the knowledge base, the MaxBufferSize should be able to reach about 750 Mo.

If I manually set the MaxBufferSize in the registry to 500 Mo, I finally can
execute my edit without error message ! So the error that I get is not linked
to the temporary disk space, but to the amount of memory required by Jet. It
seems there is a bug in the automatic setting of the MaxBufferSize parameter.

I've reproduced this problem both in Access 2000 and Access 2003.

Apparently, I am not the only one to experience this problem. A search in
Google for 'Not enough space on temporary disk' gives many pages of people
with the same problem. But the Microsoft knowledge base has no information
about that specific problem !

I have apparently found a solution, but I don't know if it is safe to set
arbitrary values in my code for MaxLocksPerFile through the
DBEngine.SetOption instruction. What value should I set so that my code works
for any database size and for any RAM available ? Is there more to this
problem ? Is it a Microsoft bug or not ?

Thanks for your answers.

 

[david epsom dot com dot au]

I can't figure why I get this error. Microsoft tells in the knowledge base

that there was a limit of about 268 Mo on the temporary files Jet????.Tmp
in
Access 95, but that since Access 97, this problem has been solved.

Which KB article?

DoCmd.RunSQL " UPDATE Field FROM Table SET Field='X' ", false
gives the error 'Not enough space on temporary disk'

RunSQL is run as a transaction, which should write to a tmp file.

CurrentDb.Execute " UPDATE Field FROM Table SET Field='X' ", false
gives the error 'System resources exceeded.

Yes, that is an out-of-memory error.

seems there is a bug in the automatic setting of the MaxBufferSize
parameter.

I'm glad you found a solution :~)

I'm interested in that KB article you refer to. Can you find it again?


Incidentally, on my reading, Jet was supposed to do a partial commit when it
ran out of locks. You would think that it would not ever give an
out-of-locks error. Of course, partial commit is really stupid, but it
would be nice to know what gives.

(david)

 

[Philippe]

"david epsom dot com dot au" escribió:

I can't figure why I get this error. Microsoft tells in the knowledge base
that there was a limit of about 268 Mo on the temporary files Jet????.Tmp
in Access 95, but that since Access 97, this problem has been solved.

Which KB article?

It is http://support.microsoft.com/kb/145772/en-us

There is also http://support.microsoft.com/kb/209940/en-us
which advises to change the MaxLocksPerFile, but it doesn't avoid the error
message: 'not enough space on temporary disk' that comes after.

I am glad you found a solution

I am not sure that my solution is safe. And I think there is more to it...
Error 'not enough space on temporary disk' is wrong and confusing. And why a
memory problem when I have 1 Go of RAM, and Jet uses only about 30 Mo ? Looks
like a bug!

 

[david epsom dot com dot au]

I'm unable to find anything about Jet 4, but in Jet 3.5 the
default maximum memory setting was (64-12)/4, which gave only
13.5 MByte. However, that was not actually a hard limit - just
the value which triggered immediate write-backs out of the cache.

To get anything bigger than 13.5, you had to set a value. Note
that '64' was the value used in that calculation if you had more
than 64MB of installed memory.

Also, if you set the value to 250000 you would not immediately
use 250MB- adding to the cache would be constantly competing
with the process that was writing back from the cache.


Regarding the error message, those error messages have always
suggested the most obvious solution, rather than an accurate
technical description. Suggestions like that do get out of
date, but as a developer, I would also ask MS not to make
changes unless there is a good reason - and making an error
message easier for a developer to understand is not a good
reason.


Regarding the 250 MB transaction limit, yes that looks like
a 'bug'. Don't hold your breath: MS doesn't want you to use
mdb's for database development.

(david)

 

[Philippe]

Hello David,

Thank you very much for your answer.

Ok to keep the same error message as before for 'not enough space on
temporary disk', but if you have a channel to Microsoft, could you suggest at
least a knowledge base article about this problem, and the related
MaxBufferSize parameter?

I think it would be helpful for many users who have to execute big queries,
on an Access database or on other DBMS.

And even if Access has not much future in itself, it is still a convenient
front end to connect to ODBC databases. It is true that since Access 2000,
the changes to Access seem essentially cosmetic.

But a correction of Jet 4.0, to let it use the correct amount of memory if
MaxBufferSize = 0, would be welcome. It seems that the formula given in the
knowledge base to compute the max amount of cache memory is false, or doesn't
apply in some cases.

 

[david epsom dot com dot au]

:~) I have no channel to MS.

(? Am I not pretty enough/ Is my heart to broken/ Do I cry too much/ Am I
too outspoken ?)


There certainly should be an article about memory management
for Jet 4.0, but at this stage I think it's probably too late.

Instead, we should be hoping that there will be information
provided about the next version they are working on now.

(david)