System.OutOfMemory Exception

[LP]

Hello,

We running VB.NET application which gets massive amounts of data from SQL
Server, loads data into DataTables, then re-arranges data into tabular
structure and outputs it to a flat file. This is just the nature of
requirements for this application. Business analyst here want certain data
to be "re-horizontalize" and served in a single flat file.
We ran this app many times before on data from different quarters, that
worked without any problems. However, now they want to run this program
against data for the whole last year. We keep getting System.OutOfMemory
Exception. I realize that it's a huge amount of data that has to be
processed in a dataset..., but is there any quick fixes for this kind of
error, system tweaks (besides more memory). We really don't have time to
redesign this application to handle data any differently at this time. They
need this file yesterday!!! Please help.

By the way; the machine that it's running on has 2gb of RAM,

 

[Chris, Master of All Things Insignificant]

Just a shot in the dark but be sure you are not running out of disk space.
I remember getting that exception and all that the problem was I was out of
disk space. Probably not it, but wanted to mention it.

Chris

 

[Guest]

Consider creating a view in SQL Server that gets the data in the correct
format and use a Process object to fire BCP to pull from this view. You will
have to assemble a proper BCP line, but the advantages are as follows:

1. Reduced load on SQL Server
2. No memory overhead issues
3. Much faster than anything you can write in VB.NET

If you are comfortable with running a batch script, you can simply put the
BCP line in a .bat file and run it. Less control, less overhead.

The problem with your application is most likely the 2 GB virtual memory
allocation per application in Windows. You can registry tweak this, but
realize that it could have adverse effects on programs other than yours. BCP
is a better option on a variety of fronts and quite easy to set up from the
command line (using SQL Server books online).
---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[LP]

I just checked over 13gb of disk space. It's powefull Dell Precision machice
with 2gb of RAM, Intel Xeon processor 3.5ghz (i think) used for batch
processing.

 

[Jeremy Chapman]

Could you do it in a loop, processing smaller sets of data broken up into
months, and just keep appending to the text file?