4

I'm experiencing a seemingly random "System resource exceeded" exception when running my code. The idea behind my program is that a third party piece of software is continuously writing data to a Microsoft Access database file (.res) - about every 30 seconds my code reads the data from this file, does some operations on it, and writes the results to our database. Unfortunately I cannot change the way the third party software writes data to files, I am stuck with Access data files.

This error occurs both on the production system running the WinForms program installed through Click-Once publishing as well as in a console test program on my development system. I get the exception even when running a query that returns a single integer and no other program or thread is touching the file which is located on my local disk.

Exception information:

System.Data.OleDb.OleDbException (0x80004005): System resource exceeded.
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteScalar()
...

Example code that reproduces the problem:

string connectionString = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\datafile.res";
string commandText = "SELECT MIN(Data_Point) FROM Channel_Normal_Table WHERE Test_ID = 1";

int connectionCounter = 0;            
object result;

while (true)
{                
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        connection.Open();
        connectionCounter++;

        using (OleDbCommand command = new OleDbCommand(commandText, connection))
        {
            result = command.ExecuteScalar();
        }

        connection.Close();
    }
}

Unfortunately the exception is not deterministic - I have seen it occur anywhere from the 4th command execution to the 6149th on the same file with the same code. It always occurs on the command.ExecuteScalar() line. If there is a resource leak in this code, please help me find it.

I have tried installing the hotfix found at http://support.microsoft.com/kb/2760394 (and made the required registry change), but it does not solve the problem. Any suggestions would be appreciated and aggressively pursued.

This is running on Windows 7, C# 4.0 (console and WinForms), 4 GB RAM

chrisfez
  • 63
  • 2
  • 9
  • 1
    Is this just example code? I ask because it seems to be a very tight loop and there might be latency issues that are causing the database connections not to be dropped by the time the loop next executes. – ChrisF Nov 21 '12 at 00:05
  • have you tried creating an index in the MS acess database to speed up the query? If the query takes too long, the previous connection might not have dropped yet... – Mitch Wheat Nov 21 '12 at 00:06
  • I will try to leave the connection opened and move the while(true) inside the using block that opens the connection. Of course I assume you have, at some point, a way to exit that loop. – Steve Nov 21 '12 at 00:11
  • @ChrisF Yes, this is just example code, but it causes the same exception as the production code. – chrisfez Nov 21 '12 at 01:02
  • @Steve In the production code there is a ~30 second delay (by timer) between closing the connection and reopening. I have tightened it up here to reproduce the exception in a matter of minutes/hours instead of days. – chrisfez Nov 21 '12 at 01:06
  • @MitchWheat Queries are generally fast, between 50 and 5000 ms, so I don't think there is a timeout issue. I am tracking using a stopwatch in my test code, but took it out in the above code for clarity. – chrisfez Nov 21 '12 at 01:07

1 Answers1

2

A couple of things I would try in your case:

  1. Since the user of the database is the application only, I would open the database in exclusive mode, this will help the driver get rid of the overhead of managing lock files (and should speed-up access to the db too).
// Share Mode=12 - exclusive mode (16 for multi-user)
string constr = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                 "Mode=12;Data Source = C:\datafile.res;user id=;password=;";
  1. Open a connection to the Access database when your program start, and leave it open until the application closes.
    In tight loops, lock-file issues creep up and cause all sorts of strange issues that are hard to debug.
    Just have a dummy table with a single record in the Access database, then open that table to read the record, but keep a permanent reference to that connection:
private OleDbCommand PermanentCommand;

void KeepLinkOpen() {
   if (PermanentCommand == null || 
       PermanentCommand.Connection == null || 
       PermanentCommand.Connection.State == System.Data.ConnectionState.Closed) {

     OleDbConnection conn = new OleDbConnection(connectionString);
     conn.Open();
     PermanentCommand = new OleDbCommand("SELECT * FROM DummyTable", conn);
     PermanentCommand.ExecuteReader(System.Data.CommandBehavior.Default);
  }    
}

void Disconnect() {
  if (PermanentCommand != null) {
      if (PermanentCommand.Connection != null) {
          PermanentCommand.Connection.Close();
      }
      PermanentCommand.Dispose();
      PermanentCommand = null;
  }
}
Renaud Bompuis
  • 16,596
  • 4
  • 56
  • 86
  • Thanks for the suggestions. For #1 this is possible for the test code, but in the production environment, the third party software is continually writing to the file and I can't hold it exclusively. I will try this in my test code to see if it affects the results. For #2, the production code doesn't have such a tight loop. It accesses multiple files, but the connections could be kept open. I've moved the connection outside the loop in my test code to see how this affects the issue. – chrisfez Nov 21 '12 at 02:38
  • I moved the connection opening and closing outside the loop and was able to query >22000 times without any exception occurring. This leads me to believe that closing the OleDbConnection doesn't free up all the resources. I've changed my production code to have a dictionary of connection objects and each connection is kept open until the file is no longer needed, similar to your code. – chrisfez Nov 21 '12 at 19:50