Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am running spatial modelling software that uses an Access database to store data needed for the computation. Each model run, data is loaded into the model from a Microsoft Access 2010 database using an SQL query and the Jet Database Engine 4.0. The connection between the software and the database is made with ODBC.

I need to run the model for thousands of locations, but after tens of model runs I get the error: “[ODBC Microsoft Access Driver] Not enough space on temporary disk”. After restarting the model, the computation runs ok for the same query and data set, but I quickly get the error again. The amount of data that is loaded differs per model run, with a maximum of ~30.000 records. When loading in only small datasets of max. 1000 records, the error does not occur.

The following did not work:

  • increasing the MaxLocksPerFile in the windows registry
  • increasing the MaxBufferSize
  • Compacting and repairing the .mdb file
  • there is enough space on my temporary disk

Interestingly, I did not have any problems with the same model on my old system (32-bit Windows XP). Only on this computer (64-bit Windows 7, 16 Gb RAM) the error occurs. The modelling software is 32-bit, and the ODBC connection is set with odbcad32.exe in “c:\Windows\SysWOW64\” directory. It seems there is a memory leak in the Windows7 version, as the same model and data works fine on an older Windows XP computer with less memory. Any ideas are greatly appreciated!

share|improve this question
    
- any luck with the solution i gave you ? –  Up_One Mar 13 at 15:30

3 Answers 3

up vote 0 down vote accepted

Generally: Replace the Access database with SQL Express. Its maximum database size is 10 GB. It might be faster too.

If you don't want to migrate, try comparing the query plans (ShowPlan debugging). I assume that Access is using another plan because it has possible access to more resources.

share|improve this answer
    
Unfortunately I cannot change the database software nor the queries; they are an integral part of the software. The developer of the modelling software is planning on migrating to newer database software in a future release, which may take a while. I will look at the ShowPlan debugging coming Monday, that might give some clues about what is going on. –  JohanB Mar 14 at 16:21
1  
I have looked at the showplan.out files generated by the ShowPlan debugging, and did not see any differences between the run in which an error occurred and the runs without error. Fortunately, I might have found a workaround; I do not get the error when I run the python file (containing the code to start the many model runs) outside of my Python compiler, in a command prompt. When running the script from PyScripter or Pythonwin, the error occurs. I have no idea how this can affect the memory error, but somehow it works.. –  JohanB Mar 18 at 15:11

This is due to the fact that MS Access tables have a limit of 2GB - this might be the issue ! The temp table fills up to 2 gb and then throws this error.
As a suggestion
- brake your query into some sub-query with their own where predicates and finally join just the results

share|improve this answer
    
Unfortunately, I cannot change the query as this is an integral part of the software. The queries never ask over 2 GB, at most a few hundred megabytes. When the same query is loaded the second time (after restarting the software) there is no error. Also, the temporary file in the temp folder never reaches 2 GB. After a few more model runs the error occurs again though, for a different query. So there is probably a data-leak in the Windows 7 version of ODBC / Jet 4.0, which was not there for Windows XP (where this error never occurred). –  JohanB Mar 14 at 16:22

I had the same problem and i found the solution, changing the version of the Asp that use the web app.

Something like this:

C:\Windows\Microsoft.NET\Framework\\aspnet_regiis.exe -s "w3svc//root"

And problem resolved, well for now..

share|improve this answer
    
Interesting.. I do not use a web app, from what I have read about this error, it seems it can be caused by various processes. I think Asp was indeed mentioned a few times as error source, but unfortunately not applicable to my problem. –  JohanB Jun 10 at 9:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.