SQL Server 2005错误701-内存不足 [英] SQL Server 2005 Error 701 - out of memory

问题描述

在SQL Server 2005上执行大约26MB的.sql文件时，我当前收到以下错误消息:

I'm currently having following error message when executing a .sql file with about 26MB on SQL Server 2005:

Msg 701, Level 17, State 123
There is insufficient system memory to run this query.

我正在使用4GB RAM，64位Windows 7 Ultimate，Core2Duo T6400(2GHz)...

I'm working with 4GB RAM, 64Bit Windows 7 Ultimate, Core2Duo T6400(2GHz)...

有没有一种方法可以执行它而不接收此消息(也许会强制SQL Server使用交换文件?)还是有一种部分执行它的方法(例如一次执行100个查询)...

Is there a way to execute it without receiving this message (maybe force SQL Server to use swap file?) or a way to execute it in parts (like 100 queries a time)...

文件基本上是一个CREATE TABLE，后面是成千上万的INSERT查询，而我有很多(使用ABC DBF Converter将.DBF文件转换为SQL查询)

The file is basically a CREATE TABLE followed by thousads of INSERT queries and I have a lot of those (converted .DBF files to SQL queries using ABC DBF Converter)

任何想法都会非常感激！

Any idea will be very appreciated!

推荐答案

实际上，这里似乎经常出现这个问题. 标记具有正确的(并且最常见的回答)，但让我尝试添加可以使之更加清楚的内容.

This question actually seems to come up every so often here. Mark has the correct (and most commonly employed) answer, but let me try to add what I can to make this clearer.

该错误消息有点误导. SQL Server告诉您它没有足够的内存来运行查询，但实际上意味着它没有足够的内存来解析查询.

The error message is a little misleading. SQL Server tells you that it doesn't have enough memory to run the query, but what it really means is that it doesn't have enough memory to parse the query.

关于运行查询 ，SQL Server可以使用它想要的所有内容-必要时提供千兆字节.解析是另一个故事.服务器必须构建一个解析树，并且仅有非常有限的可用内存.我从来没有发现任何地方记录过实际的限制，但是对于典型的充满INSERT语句的批处理，它一次只能处理几个MB.

When it comes to running the query, SQL Server can use all it wants - gigabytes if necessary. Parsing is another story; the server has to build a parse tree and there is only a very limited amount of memory available for that. I've never found the actual limit documented anywhere but for a typical batch full of INSERT statements, it can't handle more than a few MB at a time.

因此，很抱歉告诉您，但您不能使SQL Server完全按照编写的方式执行此脚本.没办法，没有办法，调整什么设置都没关系.但是，您确实有很多解决方法:

So I am sorry to tell you this but you cannot make SQL Server execute this script exactly as it is written. No way, no how, doesn't matter what settings you tweak. You do, however, have a number of options to work around it:

具体来说，您有三个选择:

Specifically, you have three options:

1. 使用GO语句. SSMS和其他各种工具将此用作批处理分隔符.不会为整个脚本生成单个解析树，而是为由GO分隔的批处理的每个段生成单独的解析树.这是大多数人的工作，并且使脚本具有交易安全性非常简单，正如其他人所证明的那样，在此我不再赘述.

1. Use GO statements. This is used by SSMS and various other tools as a batch separator. Instead of a single parse tree being generated for the entire script, individual parse trees are generated for each segment of the batch separated by GO. This is what most people do, and it is very simple to still make the script transactionally-safe, as others have demonstrated and I won't repeat here.

与其生成庞大的脚本来插入所有行，不如将数据保留在文本文件中(即，以逗号分隔).然后使用 bcp实用工具导入它.如果您需要使它可编写脚本"-即导入需要在与CREATE TABLE语句相同的脚本/事务中进行，请使用

Instead of generating a massive script to insert all of the rows, keep the data in a text file (i.e. comma-separated). Then import it using the bcp utility. If you need this to be "scriptable" - i.e. the import needs to happen in the same script/transaction as the CREATE TABLE statement, then use BULK INSERT instead. Although BULK INSERT is a non-logged operation, believe it or not, it can still be placed within a BEGIN TRAN / COMMIT TRAN block.

如果您确实确实希望INSERT是记录的操作，并且不想批量插入，则可以使用OPENROWSET，但是由于这显然是管理脚本，所以这并不是主要问题.

If you really, really want the INSERT to be a logged operation, and don't want the insertions to happen in batches, then you can use OPENROWSET to open up a text file, excel file, etc. as an ad-hoc "table", and then insert this into your newly-created table. I'm normally loath to ever recommend the use of OPENROWSET, but as this is clearly an administrative script, it's not really a major problem.

---

先前的评论表明您对#1感到不舒服，尽管这可能只是由于错误的假设，即无法在单个事务中完成，在这种情况下，请参见Thomas 的答案.但是，如果您对采用另一种方法感到束手无策，我建议您继续使用#2，创建一个文本文件并使用BULK INSERT. 安全"脚本的示例为:

---

Previous comments suggest that you're uncomfortable with #1, although that may just be because of an incorrect assumption that it can't be done in a single transaction, in which case see Thomas's answer. But if you're dead-set on going another way, I suggest going with #2, creating a text file and using BULK INSERT. An example of a "safe" script would be:

BEGIN TRAN

BEGIN TRY

    CREATE TABLE MyTable (...)

    BULK INSERT  MyTable
    FROM 'C:\Scripts\Data\MyTableData.txt' 
    WITH (
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '\r\n',
        BATCHSIZE = 1000,
        MAXERRORS = 1
    )

    COMMIT

END TRY

BEGIN CATCH

    ROLLBACK

END CATCH

希望这有助于您走上正确的道路.我很确定这涵盖了您所有可用的即用型"选项-除了这些之外，您还必须开始编写实际的应用程序或Shell脚本来完成工作，而且我认为这种复杂程度不会真的很值得.

Hopefully this helps put you on the right track. I'm pretty sure this covers all of your available "in the box" options - beyond these, you'd have to start writing actual application programs or shell scripts to do the work, and I don't think that level of complexity is really warranted here.

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