最小记录插入 [英] Minimally Logged Insert Into

问题描述

我有一个INSERT语句正在吞噬大量日志空间，以至于硬盘驱动器实际上在该语句完成之前就已填满.

I have an INSERT statement that is eating a hell of a lot of log space, so much so that the hard drive is actually filling up before the statement completes.

事实是，我真的不需要将此日志记录下来，因为它只是一个中间数据上传步骤.

The thing is, I really don't need this to be logged as it is only an intermediate data upload step.

为了论证，让我说:

• 表A:初始上传表(使用bcp填充，因此没有记录问题)
• 表B:使用INSERT INTO B from A
填充

• Table A: Initial upload table (populated using bcp, so no logging problems)
• Table B: Populated using INSERT INTO B from A

有没有一种方法可以在A和B之间进行复制而无需将任何内容写入日志?

Is there a way that I can copy between A and B without anything being written to the log?

P.S.我正在使用简单恢复模型的 SQL Server 2008 .

P.S. I'm using SQL Server 2008 with simple recovery model.

推荐答案

来自Microsoft MVP Louis Davidson:

From Louis Davidson, Microsoft MVP:

没有插入就无法插入 记录所有. SELECT INTO是 最大限度地减少T-SQL中的日志记录的最佳方法， 使用SSIS，您可以执行相同的操作 使用批量插入进行轻量级日志记录.

There is no way to insert without logging at all. SELECT INTO is the best way to minimize logging in T-SQL, using SSIS you can do the same sort of light logging using Bulk Insert.

根据您的要求，我会 可能使用SSIS，全部删除 约束，尤其是独特的 主键，加载数据， 重新添加约束.我装 在一个多小时内大约有100GB 这样，开销却非常小.一世 我正在使用批量记录恢复模型， 这只是记录新的存在 记录期间的范围，然后 您可以稍后将其删除.

From your requirements, I would probably use SSIS, drop all constraints, especially unique and primary key ones, load the data in, add the constraints back. I load about 100GB in just over an hour like this, with fairly minimal overhead. I am using BULK LOGGED recovery model, which just logs the existence of new extents during the logging, and then you can remove them later.

关键是从准系统开始 桌子，它只是尖叫.建筑 索引一旦离开，您将不会 要维护的索引，仅此一项 每个索引建立索引.

The key is to start with barebones tables, and it just screams. Building the index once leaves you will no indexes to maintain, just the one index build per index.

如果您不想使用SSIS，这一点仍然适用于删除所有约束并使用BULK LOGGED恢复模型.这样可以大大减少对INSERT INTO语句的记录，从而可以解决您的问题.

If you don't want to use SSIS, the point still applies to drop all of your constraints and use the BULK LOGGED recovery model. This greatly reduces the logging done on INSERT INTO statements and thus should solve your issue.

http://msdn.microsoft.com/en-us/library/ms191244.aspx

这篇关于最小记录插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！