SSIS Package failing

Question

Post reply

SSIS Package failing

marty.seed

SSCrazy

Points: 2661
More actions
January 19, 2010 at 9:45 am

#215809

Hello All,
I am getting the following error when I run a package on the server:
Description: The buffer manager cannot extend the file "C:\Usersobody\AppData\Local\Temp\DTS{BEFA52F4-574C-46A3-B825-0D51AA604F5B}.tmp" to length 10485768 bytes. There was insufficient disk space.
When I restart the server it runs ok.
Has anyone come across this problem that can give me an idea of how to remedy it. The package runs fine on my local.
Any help or advice is appreciated!
Marty
January 19, 2010 at 11:41 pm

This was removed by the editor as SPAM
January 20, 2010 at 10:11 pm

This was removed by the editor as SPAM
January 21, 2010 at 11:48 pm

This was removed by the editor as SPAM
June 28, 2010 at 1:47 am

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Andy Leonard SSChampion Points: 10134 More actions · Answer 1

Hi Marty,

That error seems to be indicating a low drive space issue. Do you have permissions to check free space on the C: drive?

Andy

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

marty.seed SSCrazy Points: 2661 More actions · Answer 2

Hi Andy,

I do not but have requested my DBA look into it.

Marty

marty.seed SSCrazy Points: 2661 More actions · Answer 3

We moved the DB to a larger server and it was working fine but now this error is rearing its ugly head!

Information: The buffer manager failed a memory allocation call for 10485760 bytes, but was unable to swap out any buffers to relieve memory pressure. 2 buffers were considered and 1 were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked.

marty.seed SSCrazy Points: 2661 More actions · Answer 4

Hi Stewart,

Got the answers from our DBA...

Server is x64

SQL2008 software is x64

4gb of RAM

C has 16gb of free space, 50gb capacity

I am taking three tables from one DB on one server to a Reporting DB depository/warehouse. There are approx 8 million rows in one table so I have broken that table transfer out into 4 Data Flows.

It seems to be working now after we stoped and started the server.

sscnn Valued Member Points: 70 More actions · Answer 5

stewartc-708166 (1/20/2010)
- Is the server x86, x64 or IA64?
if the package was built and compiled in x86 and deployed on x64, it will lock excessive resources (especially memory).

Hi

We're having the same problem as the original poster where packages keep failing although there is over 50 GB of free storage which for the size of the data that is going through the process is plenty. We do develop on x86 and deploy to x64, could you please elaborate on your point above?

Thanks

ashiqkhan98 SSC Enthusiast Points: 126 More actions · Answer 6

The buffer manager failed a memory allocation call for 10485760 bytes, but was unable to swap out any buffers to relieve memory pressure. 2 buffers were considered and 1 were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked.

Mercedes Benz R320 Parts

sscnn Valued Member Points: 70 More actions · Answer 7

stewartc-708166 (6/28/2010)
When an SSIS package is built on an x86 box, there are no x64 runtime components included. This includes memory management. therefore, upon execution, the package grabs as much memory as possible (up to 4GB) and tries to use this, often resulting in resource issues (especially with large sets of data).
What can be done, is to construct the package on an x86 box, copy the source to an x64 box that has the client tools installed and build it there. This will incorporate the x64 runtime components into the compiled package and allow for, inter alia, better memory management.

Sorry, but I don't think the above is necessarily correct, could you please point me to Microsoft documentation about it? SSIS packages for the most part are metadata in xml format, which is compiled at runtime on the machine it's running on. So if you create a package and deploy it to a x64 machine, the source will be compiled using x64 components given you run it in 64-bit mode not 32-bit mode.

stewartc-708166 (6/28/2010)
Buffer errors are most frequently encountered where large data sets are retrieved and certain operations, e.g. sorts, are performed thereon.
this usually causes the creation of swap files as memory is swapped out to hard drive. When the drive on which the OS is installed begins to fill up, the buffer error is encountered.

As I said, this error at least in our situation happens when there is a plenty of storage available. Somehow there is an issue with how SSIS handles temp buffer storage.