最少登录插入 [英] 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?
附:我正在使用 SQL Server 2008 和 simple 恢复模型.
P.S. I'm using SQL Server 2008 with simple recovery model.
推荐答案
来自微软 MVP 的 Louis Davidson:
From Louis Davidson, Microsoft MVP:
没有就无法插入完全记录.SELECT INTO 是在 T-SQL 中最小化日志记录的最佳方法，使用 SSIS 你可以做同样的事情使用 Bulk Insert 进行光照记录.
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这一点，开销相当小.一世正在使用 BULK LOGGED 恢复模式，这只是记录新的存在记录期间的范围，然后您可以稍后删除它们.
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屋！
