TSQL插入事务日志填充 [英] TSQL Insert Transaction Log Filling up

问题描述

需要生成一些测试数据.该插入物为800,000 X 1,000.我知道很多，但这是一个实际的应用，其中随机数是一个计算得出的数字.

Need to generate some test data. This insert is 800,000 X 1,000. I know a lot but this is a real application where the random will be a calculated number.

我该如何分解，以使事务日志无法填满?

How can I break this up so the transaction log does not fill up?

insert into sIDcrossMatch
  select 
  docSVsys1.sID, docSVsys2.sID, Abs(Checksum(NewId())) % 100 As RandomInteger 
  from docSVsys as docSVsys1 
  join docSVsys as docSVsys2
  on docSVsys1.sID <> docSVsys2.sID 
  where docSVsys1.sID < 1000
  order by docSVsys1.sID, docSVsys2.sID

它将插入一个docSVsys1.sID而不填满事务日志.

It will insert one docSVsys1.sID without filling up the transaction log.

推荐答案

由于这是您的测试数据库，因此请确保您的

Since that is your test database, make sure your Recovery model to Simple first and then let log grow as much as you could provide space to it (add more files if needed). And be sure that You understand consequences of these settings.

下一步，如果无法设置恢复模型并允许日志增长，则第一步，通过添加where子句将您的insert语句拆分为多个insert语句:

Next step, or first step if you can't set recovery model and allow log to grow, split your insert statement into multiple insert statements by adding where clause like this:

1. 插入#1:docSVsys1.sID％2 = 0/*％表示模*/
2. 插入#2:docSVsys1.sID％2 = 1/*％表示模*/

如果那还不够，请增加分隔符(2)并添加更多的insert语句.多次插入的想法是使用更少的日志空间并重用日志空间.

if that would not be enough, increase divider (2) and add more insert statements. The idea behind multiple inserts is to use less log space and reuse log space.

或者，如果可能的话，请使用SSIS并在选择查询中包含一个源组件和一个目标组件(不要忘记设置批处理大小).

Or, if possible for You, use SSIS and have one source component with your select query and one destination component (don't forget to set batch size).

这篇关于TSQL插入事务日志填充的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！