[Fixed] The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers

In this post, I’m going to show how I fixed the issue regarding the buffer in SQL Server while importing data from the ODBC.

I was importing very large data almost more than 100K row from another database to SQL Server using ODBC, It was importing data at some point like 10K rows and after that, I was getting a big error like

Information 0x4004800c: Data Flow Task 1: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 0 buffers were considered and 0 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.
(SQL Server Import and Export Wizard)

Information 0x4004800f: Data Flow Task 1: Buffer manager allocated 3 megabyte(s) in 1 physical buffer(s).
(SQL Server Import and Export Wizard)

Information 0x40048010: Data Flow Task 1: Component “Source – Query” (1) owns 3 megabyte(s) physical buffer.
(SQL Server Import and Export Wizard)

I tried a lot of things but nothing worked. I took a suggestion from my senior developer. He suggested changing the SQLEXPRESS Protocol Name Named Pipes – Enabled. I did that and you don’t believe it worked like charm. Find the below steps to make it work.

Step1

Go to Control Panel – > Administrative Tools -> Computer Management

Step2

Step3

After that try to import the data and it will fetch the data in chunks now instead of fetch all at once. Hope that will work for you guys and save your time.

Cheers!

+14

0