OleDbException System Resources Exceeded

Question

The following code executes a simple insert command. If it is called 2,000 times consecutively (to insert 2,000 rows) an OleDbException with message = "System Resources Exceeded" is thrown. Is there something else I should be doing to free up resources?

using (OleDbConnection conn = new OleDbConnection(connectionString))
using (OleDbCommand cmd = new OleDbCommand(commandText, conn))
{
    conn.Open();
    cmd.ExecuteNonQuery();
}

Loth

Answer 1

The system resources exceeded error is not coming from the managed code, its coming from you killing your database (JET?)

You are opening way too many connections, way too fast...

Some tips:

• Avoid round trips by not opening a new connection for every single command, and perform the inserts using a single connection.
• Ensure that database connection pooling is working. (Not sure if that works with OLEDB connections.)
• Consider using a more optimized way to insert the data.

Have you tried this?

using (OleDbConnection conn = new OleDbConnection(connstr))
{
    while (IHaveData)
    {
        using (OleDbCommand cmd = new OleDbCommand())
        {
            cmd.Connection = conn;
            cmd.ExecuteScalar();
        }
    }
}

Stylolite

I had a similar issue, and moving the using() statement outside of the loop not only got rid of the exception, but increased the speed drastically. Thanks. – Plutonian

The .NET Framework Data Provider for OLE DB automatically pools connections using OLE DB session pooling. This can be disabled in the connection string by setting OLE DB Services. – Ancestor

Answer 2

I tested this code out with an Access 2007 database with no exceptions (I went as high as 13000 inserts).

However, what I noticed is that it is terribly slow as you are creating a connection every time. If you put the "using(connection)" outside the loop, it goes much faster.

Pharyngoscope

Answer 3

In addition to the above (connecting to the database only once), I would also like to make sure you're closing and disposing of your connections. As most objects in c# are managed wrt memory, connections and streams don't have this luxury always, so if objects like this aren't disposed of, they are not guaranteed to be cleaned up. This has the added effect of leaving that connection open for the life of your program.

Also, if possible, I'd look into using Transactions. I can't tell what you're using this code for, but OleDbTransactions are useful when inserting and updating many rows in a database.

Rosemarierosemary

Correct disposal of objects is the whole point of his using statements. – Ancestor

Answer 4

I am not sure about the specifics but I have ran across a similar problem. We utilize an Access database with IIS to serve our clients. We do not have very many clients but there are alot of connections being opened and closed during a single session. After about a week of work, we recieve the same error and all connection attempts fail. To correct the problem, all we had to do was restart the worker processes.

After some research, I found (of course) that Access does not perform well in this environment. Resources do not get released correctly and over time the executable will run out. To solve this problem, we are going to move to an Oracle database. If this does not fix the problem, I will keep you updated on my findings.

Jannajannel

Do you have a source to back this up? – Rejuvenate

I do not remember my sources, it has been to long. But in many places throughout the net I have found many comments that this issue does exist. Sorry I do not have more information. – Jannajannel

Answer 5

This could be occurring because you are not disposing the Connection and Command object created. Always Dispose the object at the end.

OledbCommand.Dispose();

Scream

After escaping from "using" scope the object will be disposed. That's how the "using" works – Appalachian