Memory leak in OleDbCommand.ExecuteNonQuery()?

[Colin Sewell]

Maybe there's something I'm doing wrong here. I'm open to any
suggestions.

I'll see if I can explain this clearly...

I have a C# application that runs 24x7 collecting data from some
source and storing the data in a Microsoft Access database. The
problem is, every time it stores the data using a call to
OleDbCommand.ExecuteNonQuery(), the applications memory usage grows.
After a few days, the application has consumed most of the systems'
virtual memory and grows more and more sluggish and unresponsive. The
only solution right now is to restart the application every couple of
days.

Since the app uses one database and stores the same database fields
with only the data changing, I set up everything at the start of the
program. After that, as data comes in, only the stored data parameter
values change.

The first thing I do is open the MS Access database using an
OleDbConnection with a Microsoft.Jet.OLEDB.4.0 provider. I then create
a OleDbCommand with a command string that basically consists of
"INSERT INTO table (field1, field2...) VALUES (?,?...)". Next I use
OleDbCommand.Parameters.Add to add OleDbParameter data parameters that
correspond to each field to the OleDbCommand, with the proper datatype
for each field. Lastly I set the OleDbConnection in the OleDbCommand.

As the data is collected I go through each OleDbParameter and set its
Value to either the data collected or DBNull.Value if the data is
missing. Then I call OleDbCommand.ExecuteNonQuery to actually store
the data. That occurs successfully, but the virtual memory alllocation
goes up and up each time the data is stored. I monitor the memory
usage with Sysinternals ProcExp, and if I comment out the call to
actually store the data, the memory usage remains constant.

So am I doing something wrong, or is there a memory leak in
OleDbCommand.ExecuteNonQuery?

 

[Alex Passos]

Are you also removing the parameters from your parameter collection? Like

for(int i = 0; i < dbCommand.Parameters.Count; i++) {
dbCommand.Parameters.Remove(0);
}

When I use the command object I add the number of parameters via Add, then
execute, then have equally a number of Remove(0) for each parameter.

Alex

 

[Marina]

We can't say if you are doing something wrong since you didn't provide any
relevant code.

 

[Mark Rae]

So am I doing something wrong, or is there a memory leak in
OleDbCommand.ExecuteNonQuery?

I can't see (anything wrong with) your code...

 

[Colin Sewell]

Are you also removing the parameters from your parameter collection? Like

for(int i = 0; i < dbCommand.Parameters.Count; i++) {
dbCommand.Parameters.Remove(0);
}

When I use the command object I add the number of parameters via Add, then
execute, then have equally a number of Remove(0) for each parameter.

Well, no, because I would just have to add them again the next time I
have to store some data. The parameters remain the same, only their
values change.

 

[Alex Passos]

This may be applicable to you:

http://support.microsoft.com/default.aspx?scid=kb;en-us;248014

 

[Colin Sewell]

We can't say if you are doing something wrong since you didn't provide any
relevant code.

OK, I've tried to pare the code down to the essentials. minus all the
try catch blocks...

This is the setup code and gets executed once:
----------------------------------------------------------------------
private OleDbConnection con = null;

private const string SQLIns = @"INSERT INTO table
(Field1,Field2,Field3,[Field4],Field5,Field6) VALUES (?,?,?,?,?,?)";

private OleDbCommand cmdIns = new OleDbCommand( SQLIns );

private OleDbParameter[] parIns = new OleDbParameter[]
{ new OleDbParameter( "@Field1", OleDbType.DBTimeStamp ),
new OleDbParameter( "@Field2", OleDbType.VarWChar ),
new OleDbParameter( "@Field3", OleDbType.VarWChar ),
new OleDbParameter( "@Field4", OleDbType.VarWChar ),
new OleDbParameter( "@Field5", OleDbType.VarWChar ),
new OleDbParameter( "@Field6", OleDbType.DBTimeStamp ) };


for (int I = 0; I <= parIns.GetUpperBound(0); I++)
cmdIns.Parameters.Add( parIns );

con = new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + databasename );
con.Open();
cmdIns.Connection = con;
----------------------------------------------------------------------

Each time some new data comes in, it's passed to a routine as a ref
array of strings called Data[] corresponding to the parameters:
----------------------------------------------------------------------
for (int I = 0; I <= Data.GetUpperBound(0); I++)
if (Data != null)
parIns.Value = Data;
else
parIns.Value = DBNull.Value;

cmdIns.ExecuteNonQuery();
----------------------------------------------------------------------

The above routine gets called repeatedly. If I comment out the
cmdIns.ExecuteNonQuery() statement, memory requirements reach a peak
and stay there. Otherwise, every call to cmdIns.ExecuteNonQuery()
consumes a bit more memory until virtual memory is exhausted.

 

[Colin Sewell]

This may be applicable to you:

http://support.microsoft.com/default.aspx?scid=kb;en-us;248014

OK, this looks promising. I'd rather not have to edit the registry at
the customers' site, but I am having a little problem getting the
parameter set in the OledbConnection string:

con = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0;Jet
OLEDB:Max Buffer Size=1024;Data Source=database.mdb" );

fails with the message: "Could not find installable ISAM."

 

[Colin Sewell]

OK, this looks promising. I'd rather not have to edit the registry at
the customers' site, but I am having a little problem getting the
parameter set in the OledbConnection string:

con = new OleDbConnection( @"Provider=Microsoft.Jet.OLEDB.4.0;Jet
OLEDB:Max Buffer Size=1024;Data Source=database.mdb" );

fails with the message: "Could not find installable ISAM."

I've edited my registry and the solution indeed seems to be working.
According to this article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q318161

you can't set Session-Level Jet Properties in the OledbConnection.
There doesn't seem to be any way of solving this short of a registry
edit.

 

[Alex Passos]

Try it without the Jet OLEDB portion just set the Max Buffer size, that
would be my next guess.

 

[Stephany Young]

So, your app runs 24/7 and collects data from some source.

What you don't say is how often the incoming data 'appears'. e.g. Is it many
times per second, many times per minute, many times per hour, occassionally,
etc. Does it appear in bursts - a number within a short timespan and then no
more for a significant time.

If it 'appears' one sample at a time with a significant timespan between
samples then you could consider opening and closing the connection for each
sample.

If samples 'appear' in a burst with a significant timespan between bursts
then you could consider opening and closing the connection for each burst.

In general you could consider closing and reopening the connection after x
number of samples on a regular basis or after a regular timespan.

Remember that the article states that the 'problem' is per connection so
closing and reopening the connection regularly should work around the issue
without needing to modify the target machines registry.