数据归档、日志表清理、历史订单瘦身，这些任务几乎每个DBA和后端开发都会遇到。一张表积累了上亿行数据，业务方要求保留最近三个月，剩下的几千万行要删掉。很多人的第一反应是写一条DELETE FROM BigTable WHERE CreateTime < '2024-01-01'，然后在生产库上执行。结果往往是执行几个小时没动静，事务日志文件从2GB涨到200GB，磁盘告警，其他业务全部被阻塞。问题的根源不在于删除本身，而在于SQL Server的事务机制。
为什么一条大DELETE会撑爆事务日志
SQL Server为了保障事务的原子性和可恢复性，会把事务中每一行的修改都记录到事务日志（Transaction Log）里。一条DELETE语句不管影响多少行，在SQL Server眼里都是一个事务。删除一千万行，就意味着一千万行的事务日志记录要在同一时间点保持活跃，这些记录在事务提交之前都不能被截断或复用。
这就带来两个直接后果。第一，日志文件物理体积持续增长，如果磁盘空间不足，数据库会直接报9002错误并进入只读状态。第二，长事务持有大量锁，会阻塞依赖该表的查询和其他写入，即便你用的是行锁，上千万行锁累积起来也可能触发锁升级，把整个表锁住。
有人会问：我的数据库是简单恢复模式（SIMPLE），日志不是可以自动截断吗？这里有个常见误区。简单恢复模式下的日志截断发生在检查点（CHECKPOINT）且当前没有活跃事务的时候。你的DELETE还在跑，事务没提交，日志就不可能被截断，照样会把盘写满。恢复模式只能改变日志保留的时长，改变不了活跃事务占用日志这个事实。
WHILE循环分批DELETE的标准写法
解决思路很直白：把一个大事务拆成成千上万个只有几千行的小事务。每个小事务提交后，日志就能被截断复用（简单模式下）或定期备份收缩（完整模式下），锁也会及时释放，其他会话得以正常访问表。核心写法如下：
DECLARE @BatchSize INT = 5000;
DECLARE @Affected INT = 1;
WHILE @Affected > 0
BEGIN
DELETE TOP (@BatchSize)
FROM dbo.BigTable
WHERE CreateTime < '2024-01-01';
SET @Affected = @@ROWCOUNT;
-- 每批之间让出一点资源，减轻对业务的冲击
WAITFOR DELAY '00:00:00.100';
END这段代码有几个关键点值得展开。DELETE TOP (@BatchSize)里的括号不能省，因为变量形式的TOP必须加括号，这是和SELECT TOP 5000的字面量写法的区别。@@ROWCOUNT返回上一条语句影响的行数，当它变成0，说明符合条件的行已经删完，循环自然结束。WAITFOR DELAY是可选的缓冲，让SQL Server在批次之间喘口气，生产环境建议加上，尤其是业务高峰期。
批次大小怎么定？经验值在2000到10000之间。太小了循环开销占比高，总耗时拉长；太大了单批日志量依然可观，锁持有时间变长。建议先从5000开始，观察日志增长速率和阻塞情况，再逐步调整。如果表上有大量索引和触发器，往小调；如果表结构简单、非业务高峰，可以往大调。
带日期边界的进阶写法
上面这个基础写法有个隐藏性能问题：每一批DELETE都要扫描表去找符合条件的行。随着删除的推进，剩下的符合条件的行越来越少，却越来越深地藏在表里，每批耗时越来越长。一个改进办法是维护一个不断推进的删除边界：
DECLARE @BatchSize INT = 5000;
DECLARE @Boundary DATETIME = '2023-01-01';
DECLARE @End DATETIME = '2024-01-01';
DECLARE @Next DATETIME;
WHILE @Boundary < @End
BEGIN
-- 先找出这一小段时间窗内的上界，锁定要删除的范围
SELECT @Next = MAX(CreateTime)
FROM (
SELECT TOP (@BatchSize) CreateTime
FROM dbo.BigTable WITH (NOLOCK)
WHERE CreateTime >= @Boundary AND CreateTime < @End
ORDER BY CreateTime
) t;
IF @Next IS NULL BREAK;
DELETE dbo.BigTable
WHERE CreateTime >= @Boundary AND CreateTime <= @Next;
SET @Boundary = DATEADD(SECOND, 1, @Next);
END这种写法利用CreateTime上的索引做范围定位，每一批只触碰自己时间窗内的数据，避免反复全表扫描，在数据分布有时间规律的场景下效率提升明显。代价是逻辑稍复杂，且要求排序列上有合适的索引。
索引与恢复模式：两个绕不开的配套问题
分批删除的效率高度依赖索引。如果WHERE条件列上没有索引，每一批都是全表扫描，删几千万行可能要扫几万次表。所以在动手前，先确认过滤列（比如CreateTime）上是否有索引；没有的话，宁可先建一个再删，总成本通常更低。但也别建太多，因为每删一行，所有非聚集索引都要同步维护，索引越多删除越慢、日志越多。
删除完成后，还会留下大量碎片和未释放的空间。如果后续还要继续写入这张表，碎片空间可以被复用，不一定要处理；如果表要瘦身，可以用ALTER INDEX ALL ON dbo.BigTable REBUILD重建索引来回收空间。注意REBUILD本身也是大操作，同样建议放在维护窗口执行。
恢复模式方面，完整恢复模式（FULL）下，即便每批都及时提交，日志依然要靠日志备份才能截断。所以大批量删除期间，最好临时加大日志备份频率，比如从每小时一次提到每五分钟一次，让日志持续被截断回收。也有DBA会临时切换到简单模式，删完再切回完整模式并立即做一次完整备份，这在允许短暂失去时点恢复能力的场景下是可行的取舍。
分批DELETE之外的两个替代方案
如果要删除的数据占表的大头，比如一张一亿行的表要删掉九千万行，分批DELETE就不再是好选择，因为每删一行都有日志开销，索引维护成本也高。这种情况下更推荐反向迁移：把要保留的数据SELECT INTO到一张新表，重建索引，然后旧表改名或直接DROP。SELECT INTO是最小日志操作，速度快得多。
-- 反向迁移：保留少数，删除多数
SELECT * INTO dbo.BigTable_New
FROM dbo.BigTable
WHERE CreateTime >= '2024-01-01';
CREATE CLUSTERED INDEX IX_BigTable_New_CreateTime
ON dbo.BigTable_New(CreateTime);
-- 处理迁移期间的增量数据后，用切换替换旧表
EXEC sp_rename 'dbo.BigTable', 'BigTable_Old';
EXEC sp_rename 'dbo.BigTable_New', 'BigTable';
-- DROP TABLE dbo.BigTable_Old;另一个方案是分区切换（Partition Switch）。如果表按时间做了分区，删除某个时间段的数据就变成一次元数据操作：用ALTER TABLE ... SWITCH PARTITION把旧分区切到一张空 staging 表，再TRUNCATE那张表，瞬间完成，几乎不产生日志。前提是表必须提前规划好分区，属于架构层面的设计，事后补救成本较高。
总结一下选择标准：删除量占少数（比如30%以内），用WHILE循环分批DELETE最稳妥；删除量过半，考虑反向迁移；表已分区或可以改造，优先分区切换。三种方案没有绝对优劣，关键看数据比例、表的现状和可接受的停机窗口。
SQL Server分批删除事务日志WHILE循环DELETE修改时间：2026-09-03 11:01:12