Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MS SQL Buffer Manager Detected Low Virtual Memory

Posted on 2007-10-03
3
Medium Priority
?
5,701 Views
Last Modified: 2013-11-30
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
0
Comment
Question by:BartWestphal
  • 2
3 Comments
 
LVL 5

Expert Comment

by:nicolasdiogo
ID: 20013246
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
 

Author Comment

by:BartWestphal
ID: 20014964
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
 
LVL 5

Accepted Solution

by:
nicolasdiogo earned 2000 total points
ID: 20015379
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question