Article ID: 248014 - Last Review: July 28, 2003 - Revision: 1.1

PRB: Jet OLE DB Provider Consuming Too Much Memory

Retired KB ArticleRetired KB Content Disclaimer
View products that this article applies to.
System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.
This article was previously published under Q248014
Expand all | Collapse all

SYMPTOMS

After performing a large number of operations ( for example, inserts) on a Microsoft Jet database engine with the Jet 4.0 OLE DB Provider, large amounts of memory are consumed by the application. For example, performing 1000 inserts in a tight loop may reveal 100+ megabyte usage. When the connection is closed, the memory is freed.
Back to the top

CAUSE

The Jet 4.0 OLE DB Provider sets the Jet OLEDB:Max Buffer Size property to 0 by default. This allows the size of the cache to grow very large. For example, on a 256-MB computer up to 132 megabytes can be consumed by Jet. If you are using Visual C++, the Max Buffer Size property is a session property (DBPROPSET_JETOLEDB_SESSION property set) with the value of DBPROP_JETOLEDB_MAXBUFFERSIZE. These constants are defined in the Msjetoledb.h file that is included with the Microsoft Data Access SDK.

IMPORTANT: This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:
256986  (http://support.microsoft.com/kb/256986/EN-US/ ) Description of the Microsoft Windows Registry


The Max Buffer Size property control is the maximum amount of memory that can be used by the read-ahead/write-ahead Jet cache.
Note that the Max Buffer Size property doesn't have any affect on Jet 3.x files. You must set the registry to the following registry setting:

\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 3.x\MaxBufferSize
Back to the top

RESOLUTION

Set the Jet OLE DB:Max Buffer Size property to a value such as 2048 kilobytes. When using ADO, the property must be set after opening the connection. When using the Visual C++ ATL consumer templates, you must set the property using the session object.

Here is an code sample of how to use the property in a Visual Basic application:

Private Sub Command1_Click()

Dim conn As New Connection

conn.Open "provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\MyDB.mdb"
conn.Properties("Jet OLEDB:Max Buffer Size") = 500

For i = 1 To 1000
   conn.Execute "INSERT into MYTable values (1,1)", , adExecuteNoRecords
   conn.Execute "Delete from MyTable", , adExecuteNoRecords
Next i

End Sub


Following is Visual C++ ATL code which demonstrates how to set the property:

HRESULT OpenDataSource()
{
   HRESULT		hr;
   CDBPropSet dbinit(DBPROPSET_DBINIT);

   dbinit.AddProperty(DBPROP_AUTH_CACHE_AUTHINFO, true);
   dbinit.AddProperty(DBPROP_AUTH_ENCRYPT_PASSWORD, false);
   dbinit.AddProperty(DBPROP_AUTH_MASK_PASSWORD, false);
   dbinit.AddProperty(DBPROP_AUTH_PASSWORD, OLESTR(""));
   dbinit.AddProperty(DBPROP_AUTH_USERID, OLESTR("Admin"));
   dbinit.AddProperty(DBPROP_INIT_DATASOURCE, OLESTR("D:\\MYDB.mdb"));
   dbinit.AddProperty(DBPROP_INIT_MODE, (long)16);
   dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
   dbinit.AddProperty(DBPROP_INIT_PROVIDERSTRING, OLESTR(""));
   dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
   dbinit.AddProperty(DBPROP_AUTH_CACHE_AUTHINFO, true);
		
   hr = db.Open(_T("Microsoft.Jet.OLEDB.4.0"), &dbinit);
   if (FAILED(hr))
      return hr;

   hr = m_session.Open(db);
   if (FAILED(hr))
      return hr;

   CDBPropSet SessionProps(DBPROPSET_JETOLEDB_SESSION);
   SessionProps.AddProperty(DBPROP_JETOLEDB_MAXBUFFERSIZE, 512L);
   CComQIPtr<ISessionProperties> spISP = m_session.m_spOpenRowset;
		
   return spISP->SetProperties(1, &SessionProps);<BR/>
}

Back to the top

STATUS

This behavior is by design.
Back to the top

REFERENCES

The Microsoft Developer Network CD contains more information about Jet OLE DB Provider specific properties. Search on the DBPROPSET_JETOLEDB_SESSION property. This search places you into the Jet OLE DB Provider documentation where you can find out more about the provider specific properties.
Back to the top
APPLIES TO
  • Microsoft OLE DB Provider for Jet 4.0
Back to the top
Keywords: 
kbconsumer kbjet kbmdacnosweep kbprb kbprovider KB248014
Back to the top
Retired KB ArticleRetired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Back to the top