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:

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

这是数据流任务的样子

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

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?

谢谢，
伊利(Ile)

Thanks,
Ile

推荐答案

我找到了一个解决方案，问题出在SQL Server中-它消耗了太多内存.默认情况下，最大服务器内存设置为2147483647(这是默认值).由于我的服务器具有4GB RAM，因此我将此数字限制为1100 mb.从那时起，就没有内存问题了，但是我的流程任务仍然很慢.问题出在使用查阅.默认情况下，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:15h.

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

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