MS SQL Buffer Manager Detected Low Virtual Memory

I'm running a pretty basic select statement in SQL 2005 that takes data from one table in one database and copies certain columns and records to a similar but smaller table in a second database (using a Data Flow task in SSIS).

SELECT     SALES_ORD_NR, SALES_ORD_LN_NR, EDP_NO_ID, ITM_QTY, EXT_PRC_AMT, EXT_COST_AMT, ORD_ITM_STA_LGCY_CD, 'Shipped' AS ItemStatus,
              SHIP_DT AS Date
FROM         SV_MACORD_SALES_ORD_LINES
WHERE     (ORD_ITM_STA_LGCY_CD = 'O') OR
                      (ORD_ITM_STA_LGCY_CD = 'S') OR
                      (ORD_ITM_STA_LGCY_CD = 'U') OR
                      (ORD_ITM_STA_LGCY_CD = '4') OR
                      (ORD_ITM_STA_LGCY_CD = 'R')

It appears that all the data is moving, but I'm getting the following when running the .dtsx using the Execute Package Utility:

"Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 4 buffers were considered and 4 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked."

Any ideas why this might be happening and what I can do about it (if anything)?  Please keep in mind that I have very basic knowledge of SSIS and MS SQL administration, so I will need detailed information in any responses - hence the 500 points.

Thanks!
- Bart
BartWestphalAsked:
Who is Participating?
 
nicolasdiogoCommented:
as rule of thumb you should be looking for another machine to do SSIS.
i have my packages with variables that i adjust when i post the SSIS packages to the server.
while in the testing environment i have keep them low since i do not have much memory either:
these are the variables that i look after:
DefaultMaxBufferRows
DefaultBufferSize
these are explained here:
http://sqljunkies.com/WebLog/ashvinis/archive/2005/04/06/10467.aspx

engineThreads
http://blogs.conchango.com/jamiethomson/archive/2005/10/02/SSIS-Nugget_3A00_-Engine-Threads.aspx

bufferTempStoragePath, this is because i have a disk for swapping on the server
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=552382&SiteID=1

i do not have a scientific method for working out the exact parameter of these variables that i could give as it depends on the volume of data that you are transforming.
if i am using datasets with many columns or columns with great length (eg varchar( 300 ) ) and i need to do lookups for their repective values than i set the DefaultMaxBufferRows to a small value such as 2000 on the dev environment but on the server i let be around 5000. while increasing DefaultBufferSize.

hope it helps

i am sure someone will be kind enough to share some thoughts here as well

0
 
nicolasdiogoCommented:
hi,

i assume you are running this ssis in your desktop,
and it so you will need to increase the amount of virtual memory (swap disk space) on you PC.
right-click my computer => properties => advanced TAB => performance SETTINGS => advanced TAB => virtual memory CHANGE => system managed size

this solution will allow your PC to increase and manage the virtual memory as it see fit. ideally you should have 2gb ram to use SSIS as it loads of data to your of memory to complete tasks.

0
 
BartWestphalAuthor Commented:
Hi nicolasdiogo,

I actually log into the SQL server using Remote Desktop.  The server has 4 gb of RAM, and SQL uses a great deal of it.  I've seen other comments about increasing or decreasing Max Buffer size, ect. so I'm not sure which way to go.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.