none
Need to close all open ODBC connectons to SQL server when Access 2010 database closes

    Question

  • 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.

    Thursday, August 21, 2014 2:28 PM

Answers

All replies

  • Hello,

    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]

    Thursday, August 21, 2014 2:47 PM
  • 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
    Thursday, August 21, 2014 3:25 PM
  • You can run DBCC DROPCLEANBUFFERS (Transact-SQL) to clear buffer pool, but it won't solve your issue.

    You may better check the queries used in MS Access to see if you could optimize them.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, August 22, 2014 6:56 AM
  • 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 

    DBCC DROPCLEANBUFFERS

    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 FREEPROCCACHE
    DBCC FREESESSIONCACHE
    DBCC FREESYSTEMCACHE  ( 'ALL' ) 

    Friday, August 22, 2014 5:00 PM
  • SQL Server simply does not return memory it allocates except under system memory pressure.  You should configure SQL Server's max memory to accommodate the memory needs of other programs.

    Server Memory Server Configuration Options

    David


    David http://blogs.msdn.com/b/dbrowne/

    Friday, August 22, 2014 5:21 PM