SQL将1亿行表扩容至10亿行：解决事务日志已满的最优方案

表结构 #

PersonDetails
{
 ID, // 自增列
 Age int,
 FirstName varchar,
 LastName varchar,
 CreatedDateTime DateTime,
}

该表当前有约1亿行数据，需要将数据量扩充至10亿行（可复用现有数据重复插入），目的是测试在该表上创建索引的耗时。

尝试的实现代码 #

我编写了如下循环插入的SQL语句：

Declare @maxrows bigint = 900000000,
        @currentrows bigint,
        @batchsize bigint = 10000000;
        
select @currentrows = count(*) from [dbo].[PersonDetails] with(nolock)

while @currentrows < @maxrows
    begin
        insert into [dbo].[PersonDetails]
        select top(@batchsize) 
               [Age]
              ,[FirstName]
              ,[LastName]
              ,[CreatedDateTime]
        from [dbo].[PersonDetails]
        
        select @currentrows = count(*) from [dbo].[PersonDetails] with(nolock)
    end

遇到的错误 #

执行过程中出现错误：The transaction log for database 'DBNAME' is full due to 'LOG_BACKUP'（数据库'DBNAME'的事务日志已满，原因是未执行日志备份）

疑问 #

目前考虑两种调整方案：每次插入时添加延迟，或者减小批量插入的大小，哪种方案更优？是否还有其他更高效的解决办法？

一、先解决事务日志满的核心问题 #

日志满的根本原因是日志空间无法被重用（要么是恢复模式为完整/大容量日志且未做日志备份，要么是日志文件空间不足），优先处理这个问题比调整延迟或批量大小更有效：

1. 切换为简单恢复模式（推荐测试环境使用）
   简单模式下，事务日志会在检查点后自动截断，不会持续膨胀。执行以下语句：

   ALTER DATABASE DBNAME SET RECOVERY SIMPLE;
   

   注意：测试完成后如果需要恢复完整恢复模式，记得改回并做一次完整数据库备份。

2. 若必须保留完整恢复模式
   需定期执行事务日志备份，可以在循环中插入日志备份语句，或者设置定时任务自动备份，确保日志能被截断重用：

   BACKUP LOG DBNAME TO DISK = 'D:\Backup\DBNAME_Log.bak' WITH INIT;
   

二、批量大小调整比添加延迟更优 #

添加延迟只是被动等待日志备份，没有减少单事务的日志生成量；而减小批量大小能直接降低每个插入事务的日志体积，让日志更容易被处理：

• 建议将@batchsize从1000万调整为100万-500万之间，具体数值根据服务器IO性能调整。
• 每次批量插入后，执行CHECKPOINT;（简单模式下），强制触发日志截断，释放日志空间。

三、指数级复制：更快的扩容方式 #

你当前的循环每次只插入固定数量的行，需要多次扫描全表，效率较低。改用指数级翻倍复制的方式，每次复制当前全表数据，能快速逼近目标行数：

DECLARE @targetRows BIGINT = 1000000000;
DECLARE @currentRows BIGINT;

SELECT @currentRows = COUNT(*) FROM [dbo].[PersonDetails] WITH(NOLOCK);

WHILE @currentRows < @targetRows
BEGIN
    -- 计算本次可插入的最大行数，避免超过目标
    DECLARE @insertRows BIGINT = CASE 
        WHEN @currentRows * 2 > @targetRows 
        THEN @targetRows - @currentRows 
        ELSE @currentRows 
    END;
    
    INSERT INTO [dbo].[PersonDetails] (Age, FirstName, LastName, CreatedDateTime)
    SELECT TOP(@insertRows) Age, FirstName, LastName, CreatedDateTime
    FROM [dbo].[PersonDetails] WITH(NOLOCK); -- 用NOLOCK减少锁等待
    
    SET @currentRows = @currentRows + @insertRows;
    CHECKPOINT; -- 简单模式下强制截断日志
END

这种方式从1亿到10亿只需要4次插入（1亿→2亿→4亿→8亿→10亿），大幅减少了扫描表的次数，速度提升明显。

四、其他辅助优化 #

• 禁用非必要索引：插入前禁用表上的非聚集索引，插入完成后再重建，避免每次插入都维护索引，能大幅提升插入速度。
• 临时关闭自动统计更新：执行ALTER DATABASE DBNAME SET AUTO_UPDATE_STATISTICS OFF;，避免插入过程中频繁更新统计信息拖慢速度，完成后再开启。
• 预扩展数据/日志文件：提前设置数据文件和日志文件的初始大小与自动增长值，避免插入过程中自动扩容导致的等待。

内容的提问来源于stack exchange，提问作者Amir Suhel