While executing SSIS package, I got following errors,
在执行SSIS包时,我遇到以下错误,
The buffer manager failed a memory allocation call for 10484608 bytes, but was unable to swap out any buffers to relieve memory pressure. 20 buffers were considered and 20 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.
缓冲区管理器失败了10484608字节的内存分配调用,但无法换出任何缓冲区来缓解内存压力。考虑了20个缓冲区,20个被锁定。管道可用的内存不足,因为安装不够,其他进程正在使用它,或者锁定了太多缓冲区。
[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.
[DTS.Pipeline]错误:线程“SourceThread0”已退出,错误代码为0xC0047038。
[DTS.Pipeline] Error: The Data Flow task failed to create a buffer to call PrimeOutput for output "XML Source 1" (91) on component "GeneralCongfigurations" (98). This error usually occurs due to an out-of-memory condition.
[DTS.Pipeline]错误:数据流任务无法为组件“GeneralCongfigurations”(98)上的输出“XML Source 1”(91)创建调用PrimeOutput的缓冲区。由于内存不足,通常会发生此错误。
It happen when trying insert data to SQL table from an XML file with 'Script Component'.
当尝试使用“脚本组件”从XML文件向SQL表插入数据时,会发生这种情况。
How to solve it?
怎么解决?
4 个解决方案
#1
The message tells you SSIS is using 20 buffers, 10Mb each - about 200Mb together. It is not very big amount of memory, even on 1Gb machine you would not run out of memory.
该消息告诉您SSIS正在使用20个缓冲区,每个10Mb - 大约200Mb。内存不是很大,即使在1Gb机器上也不会耗尽内存。
It is likely other processes consume the rest of the memory - check the task manager. Often it is SQL Server who consumes all the memory - if you run SQL and SSIS on same machine, restrict the amount of memory SQL is allowed to consume (in SQL Server properties), leaving some memory for SSIS - I would recommend leaving at least 0.5Gb.
其他进程可能会占用内存的其余部分 - 请检查任务管理器。通常是SQL Server占用所有内存 - 如果在同一台机器上运行SQL和SSIS,则限制SQL允许使用的内存量(在SQL Server属性中),为SSIS留下一些内存 - 我建议至少留下0.5GB。
#2
You should also look at all of the log messages. In particular, Lookup transforms can log a lot of information about how much memory they're using. You can get some very detailed logs about memory allocation.
您还应该查看所有日志消息。特别是,Lookup转换可以记录大量有关它们正在使用的内存的信息。您可以获得有关内存分配的非常详细的日志。
#3
I faced the same issue. My XML source is of around 2MB. when I start the package it started throwing this OutOfMomory warning. My server has around 8GB RAM. So it was not memory issues . Current machine SQL server service was taking close to 6GB space. I could check this from Task Manager. As my SSIS package destination was connecting to difference database I did not want the current server SQL service to be running. So the moment I stopped this SQL SERVER service, my SSIS package executed successfully.
我遇到了同样的问题。我的XML源大约2MB。当我启动它时,它开始抛出这个OutOfMomory警告。我的服务器有大约8GB RAM。所以这不是内存问题。当前的机器SQL服务器服务占用了接近6GB的空间。我可以从任务管理器检查这个。由于我的SSIS包目标连接到差异数据库,我不希望当前的服务器SQL服务正在运行。所以当我停止这个SQL SERVER服务时,我的SSIS包就成功执行了。
#4
@Chris Pickford
You want to use PerfMon Counters: Memory\ Availabe Bytes/MBytes Committed Byrtes SQLServer:SSIS Pipeline\ Buffer Memory Buffers in Use Buffers Spooled
您想使用PerfMon计数器:内存\可用字节/ MBytes承诺字节SQLServer:使用缓冲区中的SSIS管道\缓冲区内存缓冲区假脱机
Also, if you've got logging on your package, look at event User:BufferSizeTuning
此外,如果您已登录包,请查看事件User:BufferSizeTuning
#1
The message tells you SSIS is using 20 buffers, 10Mb each - about 200Mb together. It is not very big amount of memory, even on 1Gb machine you would not run out of memory.
该消息告诉您SSIS正在使用20个缓冲区,每个10Mb - 大约200Mb。内存不是很大,即使在1Gb机器上也不会耗尽内存。
It is likely other processes consume the rest of the memory - check the task manager. Often it is SQL Server who consumes all the memory - if you run SQL and SSIS on same machine, restrict the amount of memory SQL is allowed to consume (in SQL Server properties), leaving some memory for SSIS - I would recommend leaving at least 0.5Gb.
其他进程可能会占用内存的其余部分 - 请检查任务管理器。通常是SQL Server占用所有内存 - 如果在同一台机器上运行SQL和SSIS,则限制SQL允许使用的内存量(在SQL Server属性中),为SSIS留下一些内存 - 我建议至少留下0.5GB。
#2
You should also look at all of the log messages. In particular, Lookup transforms can log a lot of information about how much memory they're using. You can get some very detailed logs about memory allocation.
您还应该查看所有日志消息。特别是,Lookup转换可以记录大量有关它们正在使用的内存的信息。您可以获得有关内存分配的非常详细的日志。
#3
I faced the same issue. My XML source is of around 2MB. when I start the package it started throwing this OutOfMomory warning. My server has around 8GB RAM. So it was not memory issues . Current machine SQL server service was taking close to 6GB space. I could check this from Task Manager. As my SSIS package destination was connecting to difference database I did not want the current server SQL service to be running. So the moment I stopped this SQL SERVER service, my SSIS package executed successfully.
我遇到了同样的问题。我的XML源大约2MB。当我启动它时,它开始抛出这个OutOfMomory警告。我的服务器有大约8GB RAM。所以这不是内存问题。当前的机器SQL服务器服务占用了接近6GB的空间。我可以从任务管理器检查这个。由于我的SSIS包目标连接到差异数据库,我不希望当前的服务器SQL服务正在运行。所以当我停止这个SQL SERVER服务时,我的SSIS包就成功执行了。
#4
@Chris Pickford
You want to use PerfMon Counters: Memory\ Availabe Bytes/MBytes Committed Byrtes SQLServer:SSIS Pipeline\ Buffer Memory Buffers in Use Buffers Spooled
您想使用PerfMon计数器:内存\可用字节/ MBytes承诺字节SQLServer:使用缓冲区中的SSIS管道\缓冲区内存缓冲区假脱机
Also, if you've got logging on your package, look at event User:BufferSizeTuning
此外,如果您已登录包,请查看事件User:BufferSizeTuning