SSIS - 再次出现内存不足错误 [英] SSIS - out of memory error again

问题描述

我有 cca​​ 25 个数据库，我需要将它们整合到 1 个数据库中.首先，我尝试构建一个 ssis 包，它将每个表中的所有数据复制到一个地方，但随后出现错误:

I have cca 25 databases which I need to consolidate into 1 database. First I tried to build a ssis package which would copy all data from each table into one place but then I got error:

信息:缓冲区管理器的内存分配调用失败10485760 字节，但无法换出任何缓冲区来缓解记忆压力.考虑了 1892 个缓冲区，锁定了 1892 个.管道没有足够的内存可用，因为没有安装足够多，其他进程正在使用它，或者太多缓冲区被锁定.

Information: The buffer manager failed a memory allocation call for 10485760 bytes, but was unable to swap out any buffers to relieve memory pressure. 1892 buffers were considered and 1892 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.

然后我意识到这不是一个好主意，我只需要插入新记录并更新现有记录.之后我尝试了这个选项:

Then I realized this is not good idea and that I need to insert only new records and update existing ones. After that I tried this option:

• 获取所有连接的列表.字符串
• foreach db，复制新记录并更新现有记录(需要更新的从源表复制到临时表，从目标删除它们并从临时表复制到目标表)

这是数据流任务的样子

在某些情况下，数据流处理超过百万行.但是，我仍然遇到同样的错误 - 内存不足.

In some cases data flow procceses more than million rows. BUT, I still get the same error - ran out of memory.

在任务管理器中情况如下:

In task manager the situation is following:

我必须注意，在同一台服务器上复制了 28 个数据库，并且当此程序包未运行时，sql server 仍在使用超过 1GB 的内存.我已经读到这是正常的，但现在我不太确定......

I have to note that there are 28 databases being replicated on this same server and when this package is not running sql server is still using more than 1gb of memory. I've read that it's normal, but now I'm not that sure...

我已经为我在本文中找到的 SQL Server 安装了修补程序:http://support.microsoft.com/kb/977190但它没有帮助...是我做错了什么还是这只是事情的运作方式，我想找到一个变通的解决方案?

I have installed hotfix for SQL Server I've found in this article: http://support.microsoft.com/kb/977190 But it doesn't help... Am I doing something wrong or this is just the way things work and I am suppose to find a workaround solution?

谢谢，
岛

推荐答案

我找到了一个解决方案，但问题出在 SQL Server 中 - 它消耗了太多内存.默认情况下，最大服务器内存设置为 2147483647(这是默认值).由于我的服务器有 4gb RAM，我将此数字限制为 1100 mb.从那以后，没有内存问题，但是，我的流程任务仍然很慢.问题在于使用 Lookup.默认情况下，Lookup 会选择 Lookup 表中的所有内容 - 我改变了这一点，只选择了我需要查找的列 - 它多次加快了这个过程.

I found a solution and the problem was in SQL Server - it was consuming too much of memory. By default max server memory was set to 2147483647 (this is default value). Since my server has 4gb RAM, I limited this number to 1100 mb. Since then, there were no memory problems, but still, my flow tasks were very slow. The problem was in using Lookup. By default, Lookup selects everything from Lookup table - I changed this and selected only columns I need for lookup - it fastened the process several times.

现在整个整合过程大约需要 1:15 小时.

Now the whole process of consolidation takes about 1:15h.

这篇关于SSIS - 再次出现内存不足错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！