64-bit OutOfMemoryException with SqlDataAdapter

Question

The environment is:

• 64-bit Windows
• ~50GB RAM
• .NET 3.5 SP1
• SQL 2008

The code is (essentially, from memory):

System.Data.DataTable table = new System.Data.DataTable();
SqlCommand command = new SqlCommand("SELECT XmlColumn FROM Table WHERE ID = UniqueID", Connection);
SqlDataAdapter adapter = new SqlDataAdapter(command);

adapter.Fill(table); // OOM here

The single row that's being retrieved is ~750M characters/~1.5GB of text.

Here's a partial stack trace:

System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.

at System.Data.SqlClient.TdsParser.ReadPlpUnicodeChars(Char[]& buff, Int32 offst, Int32 len, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.ReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.ReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ReadColumnData()
at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout) ...

In my experimenting, I've determined a .NET string can hold (exactly) 1,073,741,794 characters, so this column value is comfortably below that.

Any ideas on how this OOM can be happening ?? Thanks

Answer 1

Even though you have 50GB physical memory and using 64 bit OS, the maximum size of a single .NET object is still 2GB.

Perhaps you should consider whether allocating a 1.5GB+ XML blob is really a good idea...(it rarely is)