事务日志暴涨必然发生,因未分批的DELETE会生成完整日志记录且事务未提交前日志无法截断;主因包括未做日志备份、长事务未结束、大事务集中执行及自动增长设置不合理。

事务日志暴涨不是“可能”,而是必然——只要DELETE没分批,SQL Server 就会把整批操作塞进一个事务,每删一行都记完整日志。
为什么DELETE大表必然撑爆日志
SQL Server 的 DELETE 默认走完整日志记录:每行删除都要写入前像(original row)、后像(NULL marker)、事务元数据。删 500 万行,日志就写 500 万条记录,不是“慢慢涨”,是“瞬间灌满”。更关键的是,只要事务没提交,这部分日志空间就无法被 BACKUP LOG 或 CHECKPOINT 截断——哪怕你刚备份过日志,它也钉死在那里。
常见诱因包括:
- 直接执行
DELETE FROM orders WHERE create_date ,没加 <code>TOP、没循环、没COMMIT - 误以为“调大日志文件自动增长”能解决问题,结果只是把崩溃时间从 2 分钟拖到 20 分钟
- 在完整恢复模式下,长期不执行
BACKUP LOG,日志空间根本无处释放
必须用 DELETE TOP (n) + ORDER BY 循环
这是 SQL Server 唯一被生产验证过的安全路径。不加 ORDER BY 的 TOP 是危险的:SQL Server 可能每次选不同物理页,导致漏删或重复删;用 SET ROWCOUNT 已被弃用,且不兼容新版本。
正确写法要同时满足三个条件:
-
ORDER BY字段必须是索引列(最好是主键或含create_date的复合索引),否则每次排序都全表扫描 -
WHERE条件字段也必须有索引,否则每次循环都在扫全表找匹配行 - 单批大小建议
5000~10000,超过10000日志压力陡增,低于500则事务开销占比过高
示例:
WHILE 1=1
BEGIN
DELETE TOP (5000) FROM logs
WHERE create_time < '2023-01-01'
ORDER BY id;
IF @@ROWCOUNT = 0 BREAK;
WAITFOR DELAY '00:00:00.1';
END
别只盯着DELETE,还要管日志回收节奏
光分批还不够——如果日志空间不能及时释放,照样会告警。关键在于让 CHECKPOINT 和 LOG BACKUP 赶上删除节奏。
实操中必须做这几件事:
- 在循环内加
CHECKPOINT(尤其在简单恢复模式下),强制截断已提交事务的日志 - 确保完整恢复模式下,
BACKUP LOG任务正常运行,间隔不超过 15 分钟 - 临时禁用
auto_update_stats,防止删一半时统计信息自动更新拖慢速度 - 避免在高峰期执行;如需快速腾空间,可先切换为简单恢复模式(
ALTER DATABASE ... SET RECOVERY SIMPLE),删完再切回
真正容易被忽略的点
很多人试过分批,但还是爆了日志——问题往往出在“WHERE 条件没走索引”或“ORDER BY 字段没索引”,导致每次 DELETE TOP 都变成全表扫描+排序,I/O 和 CPU 全拉满,日志写入反而更猛。还有人忘了检查是否存在长事务(用 DBCC OPENTRAN),一个挂起的事务就能把整个日志空间钉死。这些细节不排查,再标准的分批脚本也救不了场。

















