I have a ssis package that has multiple large lookups without memory restriction. When running the package manually from SSMS on the same server it runs on when running automatically under the job agent, the package errors out when the server memory gets depleted by the loading of the large lookup reference data. One of the messages I get is
"An out-of-memory condition prevented the creation of the buffer object. "
Anyway, the package runs successfully when it runs automatically under the job agent.
I was curious as to why the above happens. Is that a bug or is the run time behavior different under these 2 environments by design.
js40
If you are running through SSMS, you are likley executing the package via your machine, not the server. Do you have the full SSIS client installed? (Do you have a SQL Server Integration Service service when looking at "Services" under the control panel?)|||No, i ran the package on the same server as where it runs automatically under sql agent. I started it manually from SMSS under remote desktop. The server is sql server 2005 (64 bit) with SP1 and 8gig memory.
Actually, several times it crashed SMSS and the last thing I saw just before the SMSS window just plain disappeared was some errors related to memory.
Below are different errors i got when i ran this package serveral times.
1) The Data Flow task failed to create a required thread and cannot begin running. The usually occurs when there is an out-of-memory state.
2)The system reports 99 percent memory load. There are 8588873728 bytes of physical memory with 67133440 bytes free. There are 2147352576 bytes of virtual memory with 27369472 bytes free. The paging file has 12954959872 bytes with 4205600768 bytes free.
3)A buffer failed while allocating 2048 bytes.
4)An out-of-memory condition prevented the creation of the buffer object.
js40
No comments:
Post a Comment