none
System.OutOfMemoryException Exception

    Question

  • Hi... When i try to do a search in my application I got following exception

     

    Error in ExecuteDataset - System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.
       at System.String.CreateStringFromEncoding(Byte* bytes, Int32 byteLength, Encoding encoding)
       at System.Text.EncodingNLS.GetString(Byte[] bytes, Int32 index, Int32 count)
       at System.Data.SqlClient.TdsParserStateObject.ReadStringWithEncoding(Int32 length, Encoding encoding, Boolean isPlp)
       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)
       at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
       at System.Data.SqlClient.SqlDataReader.GetValues(Object[] values)
       at System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReader.GetValues(Object[] values)
       at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
       at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
       at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
       at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

     

    When i went back into sql server and run the same query i got around 2,10,000 records. I have 2GB RAM but I am not sure why I am getting out of memory exception. I am using VB.NET 2.0.

     

    Any Idea/ Suggestion...

     

    Thanks,

     

     

     

    Friday, February 01, 2008 8:15 PM

All replies

  • I cannot understand why you need to return 210,000 records in one operation but you need to close all the connections associated with all the Data Adapters and make sure all the DataReaders are wrapped with the second using statement which calls dispose for you automatically.

     

    Friday, February 01, 2008 8:25 PM
  • What is the max datalength of your table row?

     

    You may need to use conditional loading instead of loading all at once.

     

    Friday, February 01, 2008 8:26 PM
  •  

    These all are under a condition. Ths origional data is much bigger than this.
    Friday, February 01, 2008 9:22 PM
  • Hello,

     

    Maybe that's an error, but what's the value of Maximum WorkingSet of your application ?

     

    Be careful if you have 2 millions records, if each record needs 1000 bytes you are reaching the limit of your RAM

     

    Have a look with the taskmanager to see if  your physical RAM is not enough

     

    A question : where resides your sql server instance ? ( on your computer or on another computer )

     

    I have seen that the exception is

    Error in ExecuteDataset - System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.
       at System.String.CreateStringFromEncoding(Byte* bytes, Int32 byteLength, Encoding encoding)
       at System.Text.EncodingNLS.GetString(Byte[] bytes, Int32 index, Int32 count)

      at System.Data.SqlClient.TdsParserStateObject.ReadStringWithEncoding(Int32 length, Encoding encoding, Boolean isPlp)
       at System.Data.SqlClient.TdsParser.ReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj)

    I think that you have a problem when Sql Server acceeds to a fielf with type nvarchar ( maybe nvarchar(MAX) )

    Is it possible to have a description of your table ?

     

     

    Have a nice day

     

    Sunday, February 03, 2008 8:31 PM
  • If you're trying to fill a DataSet with 2.1 million rows, it's not terrifically surprising that you're running out of memory.  Remember that the DataRow is not just an array of data.  It contains state and version information about each row, and each row is also pointed to by one or more indexes that the DataTable is maintaining internally.

     

    If you need to keep information about 2.1 million things in memory, you probably don't want to need to use a DataTable for the purpose.  All of the operations that you'd need a DataTable for - data binding, state and version management, filtering, sorting - will run slowly with that many rows, and will often grab huge chunks of memory as they run, too.  A single statement (DataRow[] r = t.Select("1 = 1")) can eat up 8MB or so.

     

    It may make more sense to build a lightweight class with a very few fields, and use a SqlDataReader to execute the query, create an object for every row it reads, and add the row to an in-memory collection like a list or dictionary.  That will give you much more fine-grained control over the amount of memory your program uses.

     

    That said, my instinct is that if you're moving 2.1 million of anything over a wire, that's probably a design defect.  There are certainly applications where it's not, don't get me wrong.  But the first thing I would do in your case is figure out if I can use a smaller working set of rows.  Is the application aggregating data?  Maybe it can be aggregated in a query instead.  That sort of thing.
    Sunday, February 03, 2008 11:52 PM
  • Hello,

     

    Thanks to Robert Rossney for your clear explanation.

     

    As my english is far from to be perfect,i can't explain like you did.

     

    I always struggled againt this bad habit of considering that memory is no more a problem.

    I know that is an error.

    I remember that a beginner programer succeeded to crash a HP-UX with 32 Go because he have thrown directly a request which got 1 000 000 records on Oracle 9i.

    I think that the problem is the people have everything to do simple and effective programs but they often don't want to think  what's happening on the server.

    I's true that a SqlDataReader is easy to use. The only defect is that you can go backwards and so you have to treat your rows quickly and somtimes it's difficult to have time to think about the best way to organize the progs.

     

    Have a nice day

     

    Monday, February 04, 2008 6:34 AM
  • Hi All,

     

    I'm facing the same issue....The requirement is to get the data from the SQL Server 2005, perform some operations on it and then write it in a txt file with | (pipe) as the de-limiter.

     

    Appreciate some solution on this...

     

    Thanks in advance.

    Tuesday, February 05, 2008 10:55 AM
  • Hello,

     

    i will have a look on your problem but not immediately as i have to go to the post-office before its closure.

     

    See you latter

     

    Tuesday, February 05, 2008 1:25 PM
  • Create Views for just the data you need for each operation and use the View as you datasource and you may need to do some paging.

     

    Tuesday, February 05, 2008 2:20 PM
  • Hello Caddre,

     

    As usually, i have forgotten the views to do a fine selection.

     

    A stupid question, i know that's possible to do paging, but i lost the paper where i've noted how to do paging.

    Please , could you give some links about it ?

     

    If you prefer i create a thread, i will do it.

    Thank you beforehand

     

    Have a nice day

    Tuesday, February 05, 2008 2:35 PM
  • A View is the best tool to move data in specific columns because if you are pulling data from many tables with a View you have one Virtual table to access.  SQL Server 2005 comes with the RowNumber function that lets you page through large dataset more efficently because in a web application it is very easy to get out of memory exceptions.  In SQL Server you can use the TOP clause and RowNumber to return subset of data as need, here are three links with code for web application because I am a web developer.

     

    http://www.4guysfromrolla.com/webtech/042606-1.shtml

     

    http://aspnet.4guysfromrolla.com/articles/031506-1.aspx

     

    http://weblogs.asp.net/scottgu/archive/2006/01/07/434787.aspx

     

    Tuesday, February 05, 2008 3:37 PM
  • If all that you're going is reading rows and writing them to a text file, you should consider using a SqlDataReader to fetch the rows from the result set one at a time, rather than pulling all of them into a DataTable.

     

    Tuesday, February 05, 2008 8:20 PM
  •  Caddre wrote:

    A View is the best tool to move data in specific columns because if you are pulling data from many tables with a View you have one Virtual table to access.  SQL Server 2005 comes with the RowNumber function that lets you page through large dataset more efficently because in a web application it is very easy to get out of memory exceptions.  In SQL Server you can use the TOP clause and RowNumber to return subset of data as need, here are three links with code for web application because I am a web developer.

     

    http://www.4guysfromrolla.com/webtech/042606-1.shtml

     

    http://aspnet.4guysfromrolla.com/articles/031506-1.aspx

     

    http://weblogs.asp.net/scottgu/archive/2006/01/07/434787.aspx

     

     

    Thank you very much. I'm only a beginner with ASP.Net but i think i will need time to "digest" all these documentation

     

    And have a nice day  ( for me it's near the time to go sleeping )

    Tuesday, February 05, 2008 8:35 PM