Memory leak in sqloledb with MDAC 2.8 SP2?

Question

Hi,

I am seeing unmanaged memory leaking when using the sqloledb driver from C# (.NET 2.0) on Windows Server 2003 machines that have MDAC 2.8 SP2 installed.  There appears to be a problem in this driver where it is necessary to always attempt to read past the end of a ResultSet in order for memory allocated for the current row to be released - Dispose() will not be sufficient if this has not been done.  For example, in a typical situation where it is known that a query will return at most one row we would normally call Read() once on the ResultSet object, handle either of the two possible cases (a row was returned or not) and then close the ResultSet.  In order to not leak memory I need to call Read() a second time if the initial Read() was successful (which will then return false) before closing the ResultSet.

While performing these additional reads is a workaround for the problem this does seem like a bug in the driver - is there a known solution for this which does not involve changing all our code? We do have to use OLEDB as opposed to sqlclient due to the need to support other DBMSs not just SQL Server.

Thanks,

Tom

Answer 1

Hello,

I have just found this thread about a possible (?) memory leak with SQL OleDb driver ( MDAC 2.8 SP2 Windows 2003 )

http://social.msdn.microsoft.com/Forums/en-US/vcgeneral/thread/243f4d1b-d4fc-4247-abc8-deb484d8e3d3 

If i found something more useful, i will post it.

How do you know that you find a unmanaged memory leak ? Could you give more explanations ? It would make easier researches about your problem.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 2

Hi Papy,

Thanks for the response. I originally became aware of this issue when we saw memory usage for w3wp.exe increasing steadily on the affected system causing the application pool to hit its recycle limit maybe twice a day.  By comparing the performance counters for .NET bytes in all heaps with private bytes for the process we could see that the managed memory usage never exceeded a certain point but the total memory usage was steadily increasing.  By using the leak monitoring feature in the Microsoft DebugDiag tool we identified that most of the memory seemed to be being leaked from the msdatl3 memory manager and the sample call stacks showed that the leaks seemed to be coming from sqloledb.

The most convincing evidence is that the problem is reproducible using a simple test case:

using System;
using System.Data.OleDb;

namespace MemoryLeakTest
{
	class Program
	{
		const string CONNECTION_STRING = "Provider=sqloledb;Data Source=YOURSERVER;Initial Catalog=YOURDATABASE;User Id=YOURUSER;Password=YOURPASSWORD";

		static void Main(string[] args)
		{
			bool readUntilEnd = args.Length > 0;

			for (int i = 0; i < 20000; i++)
			{
				OleDbConnection con = new OleDbConnection(CONNECTION_STRING);
				con.Open();
				OleDbCommand command = new OleDbCommand("SELECT * FROM YOURTABLE", con);
				OleDbDataReader reader = command.ExecuteReader();
				if (!reader.Read())
					throw new Exception("No rows returned");
				if (readUntilEnd)
					while (reader.Read()) ;
				reader.Close();
				reader.Dispose();
				command.Dispose();
				con.Close();
				con.Dispose();
			}

			Console.WriteLine("Completed test successfully");
		}
	}
}

This code leaks memory at a steady rate on my WS2003 system with MDAC SP2 (version 2.82.3959.0) but does not leak memory on my Windows 7 development system.  I have tested it against SQL Server 2005 and SQL Server 2000 databases, the effect is the same. It also does not leak memory if a command line parameter is supplied to indicate that the program should read until Read() returns false. I would be very interested to know if anyone does or doesn't see the same behaviour with this code with that driver version.

Thanks,

Tom

Answer 3

I've identified a solution to this which is to install the SQL Native Client drivers e.g. from here: http://www.microsoft.com/download/en/details.aspx?id=3522, and then use SQLNCLI in the connection string instead of SQLOLEDB. I didn't try this originally because I hadn't realized that SQL Native Client provided an alternative OLE DB driver to SQLOLEDB.