We have a package that performs data extraction from multiple external systems. It has been running with no issues for a long time. A few days ago, it failed with the following error:

The system reports 9 percent memory load. There are 1099463696384 bytes of physical memory with 991419998208 bytes free. There are 2147352576 bytes of virtual memory with 174698496 bytes free. The paging file has 1153150787584 bytes with 1033803776000 bytes free.

I have read a lot of suggestions on the internet regarding setting max server memory but as it has been running for all this time with the configuration, I am not sure why that would be a factor now. I did change it to a lower number but set it too low and SQL Server would start up. Had to go thru single user mode to reset it back. The server has 1TB RAM.

I also saw something about setting the buffers but I am unable to find where to set that.

The package runs fine within Visual Studio but fails when run thru SQL Server Agent. Also, although it is a 64bit server, everything has been configured to run as 32bit.

Disclaimer: I am programmer and not a DBA so I am just a novice trying to figure this out cold so all suggestions are welcome but please keep that in mind when submitting your responses. thanks!

  • So you realize running in 32-bit only gives you 2GB of RAM for your process to run right? Calculate the bytes in that message There are 2GB of virtual memory with 0.16GB free. Is it likely that the amount of data your package has been handling has grown? – user507 Jan 9 '17 at 15:12
  • I would prefer to run as 64bit but I was not able to get that to work properly. I know it was most likely my lack of experience. Do you have experience with that? If in 32bit, what should it be set at? – Greg Jan 9 '17 at 15:27

There may be several reasons:

  • Check if the Page file for the system is configured properly, you might get this error if there is no page file or if page file is very small

  • Change the lookup cache mode from Full to Partial or no cache mode.

  • Migrate to 64-Bit SSIS if you are in 32-BIT. Remember you have to migrate all the drivers and providers used in connections. If you are in 64 BIT system and getting above errors while executing packages from BIDS then check if you have set package to run in 64bit run time ( Run64bitruntime = True in Integration services project properties page).

  • Divide the package in to multiple child packages (Execute package task –> ExecuteOutOfProcess =True)

  • Tweak DefaultBufferMaxRows and DefaultBufferMaxSize size.

I hope that it helps.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.