Databases
StackOverflow2013
Postsdbo
POSqlClient returning strange OOM exception? C# .NET 4
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSqlClient returning strange OOM exception? C# .NET 4
    primarykey
    data
    text
    <p>I am working on some enterprise applications that crunches large amount of data each day and to do that it has WINDOWS SERVICE application written in C# .NET 4. It also has connection to SQL SERVER 2008 R2 but for some reason it (randomly) throws me this error in synchronization table which stores JSON serialized data:</p> <pre><code>Exception of type 'System.OutOfMemoryException' was thrown. at System.Data.SqlClient.TdsParser.ReadPlpUnicodeChars(Char[]&amp; 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) at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i) at System.Data.SqlClient.SqlDataReader.GetValues(Object[] values) </code></pre> <p>This table is fairly general table to keep LOB data:</p> <pre><code>CREATE TABLE [dbo].[SyncJobItem]( [id_job_item] [int] IDENTITY(1,1) NOT NULL, [id_job] [int] NOT NULL, [id_job_item_type] [int] NOT NULL, [id_job_status] [int] NOT NULL, [id_c] [int] NULL, [id_s] [int] NULL, [job_data] [nvarchar](max) NOT NULL, [last_update] [datetime] NOT NULL, CONSTRAINT [PK_SyncJobItem] PRIMARY KEY CLUSTERED) </code></pre> <p>LOB record that is failing has 36.231.800 characters of data in <code>job_data</code> column, which is (if we say that 1 character is 2 bytes, UTF-8) about 70MB of data which is not much.</p> <p>Please consider that changing storage of data for job (e.g. disk) or something similar is not an option for me. I would like to fix this error so if anyone knows anything please help! </p> <p>Also this error happens randomly on the same data, the system running is vmWare-vCloud that is, I think, some big blade system. We have about 6GB of RAM dedicated for our vm (service at most uses about 1-2GB), service is compiled as x64 and system is x64 Windows 2008R2 Standard. I have made sure that no single object has more than 2GB in memory so that is not it, also error is inside SqlClient and in my 15y of dev experience I have never seen it and Google turns out nothing. Also the error is not on DB side since DB has over 32GB of RAM and uses only 20GB peak. For specifics that I use in this system which are not usual is multi-threading and GC.Collect() after each job step (there are multiple steps on data).</p> <p>EDIT:</p> <p>Here is the full code that is doing this problem:</p> <pre><code> internal static void ExecuteReader(IConnectionProvider conn, IList destination, IObjectFiller objectBuilder, string cmdText, DbParameterCollection parameters, CommandType cmdType, int cmdTimeout) { IDbCommand cmd = CreateCommand(conn.DBMS, cmdText, parameters, cmdType, cmdTimeout); cmd.Connection = conn.Connection; bool connIsOpennedLocally = EnsureOpenConnection(conn); try { AssignExistingPendingTransactionToCommand(conn, cmd); using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleResult)) { objectBuilder.FillCollection(reader, destination); PopulateOutputParameterValues(parameters, cmd); } } finally { CloseConnectionIfLocal(conn, connIsOpennedLocally); cmd.Dispose(); } } ... private void FillFromAlignedReader(ICollection&lt;TEntity&gt; collection, IDataReader openedDataReader, IDbTable table) { // Fastest scenario: data reader fields match entity field completely. // It's safe to reuse same array because GetValues() always overwrites all members. Memory is allocated only once. object[] values = new object[openedDataReader.FieldCount]; while (openedDataReader.Read()) { openedDataReader.GetValues(values); TEntity entity = CreateEntity(table, EntityState.Synchronized, values); collection.Add(entity); } } </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USlord.fist
    1. USlord.fist
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSqlClient returning strange OOM exception? C# .NET 4
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCan you clarify what you mean by "crunches large amount of data"> Are you reading from the table you have advised and doing something with the data? Or are you reading from other tables and writing to this table? What are you doing with your data once it has been *crunched*, does it stay in memory before a final flush, or is it written and flushed from memory on a row by row basis?
      singulars
      1. POSqlClient returning strange OOM exception? C# .NET 4
      1. USGarethD
    2. COI am loading the data from SQL Server to DataTable. This is what breaks then. It is simple select statement. Before and after I am doing some calculations with the data. Basically it goes like this: 1) Load the data from NoSQL db (Couchbase) 2) Aggregate data with map-reduce 3) Serialize the aggregate result object to JSON object 4) Save to db to this table 5) Go to next step that loads the JSON from SQL (here it breaks with OOM)
      singulars
      1. POSqlClient returning strange OOM exception? C# .NET 4
      1. USlord.fist
    3. COWhen you say *I have made sure that no single object has more than 2GB in memory*, does that include the DataTable in step 5? Could you achieve the same thing with a forward only SqlDataReader so that you only have one row at a time in memory? I think it would be useful to see the code block that is throwing the error as well as the stack trace.
      singulars
      1. POSqlClient returning strange OOM exception? C# .NET 4
      1. USGarethD
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload