Hi there, we are currently having an issue with several Access 2010 databases that are using pass through queries to a SQL server 2008 database.
When the pass through queries runs the memory on the server will spike. After the query completes & even after Access has been shut down, the memory is not released until we restart the SQL DB services. (this can be a select, update query)
What I would like to do is create code that will close all open ODBC connections to the DB when the Access database closes in hopes this will help release the memory when the database closes.
When you close MS Access all before used SQL Server Connections will be closed as well; no need to implement any code.
By default SQL Server releases allocated only under OS pressure, never else. You could config the max memory usage of SQL Server; see Server Memory Server Configuration Options and Server Memory Options
Olaf Helper[ Blog] [ Xing] [ MVP]
Olaf, thanks for the reply. This server only has 6GB of memory. It will be under 1GB before the Access session starts. It can then max out just under the physical 6GB & stays there after Access has ended.
The server is then very slow for anything else to run bet that another Access session, external connection from Excel, Notepad, ...
So far the only solution we have found is to restart the SQL services which is not a optimal solution.
In looking at the links you provided, I don't think this will solve the issue. If we set this at 3GB max for example, I would think we would still be in the same situation & reduced performance.
Am I missing something here or is there another way to get the SQL server to release the resources that are no longer being used?
- Edited by jhimes Thursday, August 21, 2014 3:26 PM spelling
The query can be as simple as Select Field from table where field = 'Value'.
It is not a query optimize issue but more of a memory leak where memory used when Access connects to the SQL server is not being returned to the system after Access closes.
I have tried running
from both a PassThrough in Access & directly in SQL Server Management studio, this did not help.
Am also trying several other commands but no luck either.DBCC DROPCLEANBUFFERS
DBCC FREESYSTEMCACHE ( 'ALL' )