MS SQL Buffer Manager Detected Low Virtual Memory

Posted on 2007-10-03
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).

              SHIP_DT AS Date
                      (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.

- Bart
Question by:BartWestphal
    LVL 5

    Expert Comment


    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.


    Author Comment

    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.
    LVL 5

    Accepted Solution

    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:
    these are explained here:


    bufferTempStoragePath, this is because i have a disk for swapping on the server

    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


    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now