PRB: Jet OLE DB Provider Consuming Too Much Memory (248014)

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

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/>
}

PRB: Jet OLE DB Provider Consuming Too Much Memory (248014)

SYMPTOMS

CAUSE

RESOLUTION

STATUS

REFERENCES