Access ODBC driver temporary file grows to 2Gb limit over time (1 Viewer)

[rob_har]

Hi all,

I have an application using an MS Access 2000-2003 database (.mdb file) via the ODBC Jet engine driver version 4. I have a prepared SQL statement which issues a simple SELECT query with 2 parameters. The database file itself is only ~2Mb.

​

I bind the 2 parameters (SQLBindParameter), bind 2 columns (SQLBindCol) in which to hold results (row size = 1) and finally execute the query. I fetch the results which are as expected. I then call SQLCloseCursor(stmt), SQLFreeStmt(stmt, SQL_RESET_PARAMS) and SQLFreeStmt(stmt, SQL_UNBIND) because I want to reuse the statement handle as the MSDN docs advise. However, if I run this simple bit of code repeatedly over a significant period of time (6-7 days) I notice a steadily growing temporary file typically called JETxxxx.tmp in the temp files folder. Once this file reaches 2GB the ODBC calls on the above statement handle fail with some sort of resource exceeded error.

​

If I repeat the above but reallocate the statement handle on each iteration the problem disappears. However, the MSDN docs imply that you suffer a performance hit as the driver is freeing and reallocing resources for the statement.

​

No changes are being made to the Access database, this is the only application accessing it to perform a SELECT. I didn't want to use SQL server to minimise installation dependencies at this time. Has anyone else noticed this problem? Is there a solution? There are workarounds, as mentioned above, but potentially I will be executing a large number of queries very frequently so want to minimise the penalties incurred. So unless I'm mistaken, the Jet engine is leaking space in this temporary file eventually reaching its self imposed limit.

​

Thanks in advance,​

Rob.​

 

[Banana]

My suggestion would be to allocate & dellocate the handle at open & close of the connection, so even though you may do several iterations, it's allocated once for a session, and hopefully a single session won't run it over 2 GB and clean it up when it's closed.

HTH.

 

[rob_har]

Hi Banana,

Thanks for your swift response. I must admit I failed to include some of the detail in the OP:

The application is intended to be long running and hence sit on the user's desktop for weeks at a time, maybe I'm going for the 'gold plated' solution but who wants to make assumptions as to when a user will switch off/reboot their PC? Thus, only one ODBC connection is made and all of the statement handles prepared at application start. This DB connection remains open for the application lifetime. It's about 6-7 days in that after executing some of these statements a few thousand times that the Jet temp file gets huge.

Maybe I'm not using ODBC the way I'm supposed to? A current somewhat dirty workaround is to free and re-prepare the statement handles after 10000 uses. Unfortunately, I can't seem to find any further information about this beyond some only very slightly related articles concerning ASP.NET query resultsets being too large for the 2GB Jet file.

I think you may be right, the only surefire solution atm is to either periodically clean up the connection or all of the statement handles.

Thanks,
Rob.

 

[Banana]

Rob, you've already answered it yourself; if connection is to be long-lived, then reallocate it periodically whether by every 10,000 uses or by a fixed time (e.g. every midnight).

Hopefully, this will help. Feel free to post back if you're having specific problems.