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?

 

[webbertsolutions]

LP,

We have the same thing. Don't exactly know what the problem is but let me
tell our results. I believe it to be related to the Database as we are
processing vast amounts of data as well.

Machine 1
2 cpu - Hyperthreaded
2 Gig - Memory

Machine 2
2 cpu - Hyperthreaded
4 Gig - Memory
SQL Server 2000

Using Perfmon we monitored the memory usage of Machine 1 and never saw it
peak over 1 Gig of memory but still received the "OutOfMemory" Exception.
As best as I have been able to find, it is not memory but a resource issue.
For instance, using up all of your file handlers or opening too many connections
to your database.

As I said, still looking for the answer but maybe this will help.

Cheers,
Dave

 

[Tad Marshall]

Another possibility is address space issues caused by DLLs that get loaded
into your process. If you can run something like Process Explorer from
http://www.sysinternals.com you can see the stuff loaded into processes, and
where they all are in your address space. One program I use allocates a
single block of virtual memory, and the largest block it can get on my
machine is 1.1 GB no matter how much RAM I have. The problem is a bunch of
DLLs that are loading at their preferred addresses but which fragment the
program's address space so that the largest contiguous block is "only" 1.1
GB.

I don't have an easy fix though.

Tad