SQL Server中以10000行分批插入方式解决‘Active Transaction Full’错误的可行性咨询

你的问题核心是一次性处理4.5亿行的大事务撑爆了事务日志，导致Active Transaction Full错误——分批插入（每次10000行）能把大事务拆成多个小事务，每个小事务完成后日志可以被及时清理，完美规避这个问题。

下面给你具体的实现方案，适配你的原查询逻辑：

方案一：使用WHILE循环+TOP分批（兼容所有SQL Server版本） #

-- 先把去重后的待插入数据存入临时表，避免每次循环重复执行JOIN和DISTINCT（大幅提升性能）
SELECT DISTINCT A.*
INTO #TempDossierRecouvrement
FROM STOCK_Pleiade.stock.TDossierRecouvrement A
INNER JOIN STOCK_Pleiade.stock.TClientIndividuel B ON A.RefUnClient = B.OID;

-- 给临时表加自增主键，方便分批读取
ALTER TABLE #TempDossierRecouvrement ADD BatchID INT IDENTITY(1,1) PRIMARY KEY;

DECLARE @BatchSize INT = 10000;
DECLARE @CurrentStart INT = 1;
DECLARE @TotalRows INT = (SELECT COUNT(*) FROM #TempDossierRecouvrement);

WHILE @CurrentStart <= @TotalRows
BEGIN
    BEGIN TRANSACTION;
    
    INSERT INTO ZED_COTIS.stockdenorm.[DossierRecouvrement_Ind]
    SELECT *
    FROM #TempDossierRecouvrement
    WHERE BatchID BETWEEN @CurrentStart AND @CurrentStart + @BatchSize - 1;
    
    COMMIT TRANSACTION;
    
    -- 可选：给日志留一点截断时间，避免短时间内日志压力过大
    WAITFOR DELAY '00:00:01';
    
    SET @CurrentStart = @CurrentStart + @BatchSize;
    PRINT '已插入 ' + CAST(@CurrentStart - 1 AS VARCHAR) + ' 行，剩余 ' + CAST(@TotalRows - (@CurrentStart - 1) AS VARCHAR) + ' 行';
END

-- 清理临时表
DROP TABLE #TempDossierRecouvrement;

方案二：使用OFFSET FETCH（SQL Server 2012及以上版本可用） #

如果你的SQL Server版本是2012+，可以用更简洁的分页语法：

SELECT DISTINCT A.*
INTO #TempDossierRecouvrement
FROM STOCK_Pleiade.stock.TDossierRecouvrement A
INNER JOIN STOCK_Pleiade.stock.TClientIndividuel B ON A.RefUnClient = B.OID;

ALTER TABLE #TempDossierRecouvrement ADD BatchID INT IDENTITY(1,1) PRIMARY KEY;

DECLARE @BatchSize INT = 10000;
DECLARE @Offset INT = 0;
DECLARE @TotalRows INT = (SELECT COUNT(*) FROM #TempDossierRecouvrement);

WHILE @Offset < @TotalRows
BEGIN
    BEGIN TRANSACTION;
    
    INSERT INTO ZED_COTIS.stockdenorm.[DossierRecouvrement_Ind]
    SELECT *
    FROM #TempDossierRecouvrement
    ORDER BY BatchID -- 使用OFFSET必须指定ORDER BY
    OFFSET @Offset ROWS FETCH NEXT @BatchSize ROWS ONLY;
    
    COMMIT TRANSACTION;
    
    WAITFOR DELAY '00:00:01';
    
    SET @Offset = @Offset + @BatchSize;
    PRINT '已插入 ' + CAST(@Offset AS VARCHAR) + ' 行，剩余 ' + CAST(@TotalRows - @Offset AS VARCHAR) + ' 行';
END

DROP TABLE #TempDossierRecouvrement;

关键注意事项 #

• 临时表的必要性：原查询中的DISTINCT和JOIN操作本身就很耗时，如果每次循环都执行一次，会大幅拖慢整个过程。先把去重后的结果存入临时表，后续分批只从临时表读取，性能提升非常明显。
• 事务日志优化：如果目标数据库是简单恢复模式，每个小事务提交后日志会自动截断；如果是完整恢复模式，记得在分批过程中定期做日志备份，避免日志文件持续膨胀。
• 索引优化：插入前可以暂时禁用目标表的非聚集索引（ALTER INDEX ALL ON ZED_COTIS.stockdenorm.[DossierRecouvrement_Ind] DISABLE;），插入完成后再重建（ALTER INDEX ALL ON ZED_COTIS.stockdenorm.[DossierRecouvrement_Ind] REBUILD;）——禁用索引能大幅加快插入速度，重建索引比边插边维护索引效率高得多。
• 避免重复插入：如果这个分批操作可能中断后重启，建议在目标表加唯一约束，或者在临时表处理时确保数据唯一，避免重复插入。
• 错误处理：可以在循环里加入TRY/CATCH块，遇到错误时回滚当前事务并输出错误信息，比如：

BEGIN TRY
    BEGIN TRANSACTION;
    -- 插入逻辑
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT '插入失败：' + ERROR_MESSAGE();
    BREAK; -- 或者根据需求选择继续下一批
END CATCH

这个方案完全能解决你的Active Transaction Full问题，而且相比一次性插入，对系统资源（CPU、内存、日志空间）的压力小很多，不会影响其他业务的正常运行。

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