一、事件背景

• 数据库：XXX

• 现象：

• 数据库 mdf 文件持续增大

• 已删除大量历史数据，但 磁盘空间未释放

• 数据文件大小：427GB

• 事务日志文件在操作过程中增长明显

二、问题分析结论

本次问题属于 典型 SQL Server 空间回收认知误区：

DELETE / 清理数据 ≠ 释放 mdf 文件空间

具体原因：

1. 大量历史数据被 DELETE

2. 表和索引内部产生大量 空洞页（unused space）

3. SQL Server 默认只标记空间可复用，不会自动收缩数据文件

4. 在 不允许锁表、不做索引重建（REBUILD） 的前提下：

• 可回收空间受限

• Shrink 能力有限

三、排查与判断过程（关键步骤）

1️⃣ 查看数据库文件大小（基线）

USE xxx;GOSELECT    name        AS logical_name,    physical_name,    size * 8 / 1024       AS size_mb,    size * 8 / 1024 / 1024 AS size_gbFROM sys.database_files;
文件
大小
xxx.mdf
427 GB
xxx_log.ldf
59 GB

2️⃣ 确认“删除数据但空间未释放”的原因

通过 sp_spaceused、表级统计发现：

USE xxx;GOEXEC sp_spaceused;

SELECT    s.name  AS schema_name,    t.name  AS table_name,    SUM(p.rows) AS rows,    SUM(a.total_pages) * 8 / 1024 AS total_mb,    SUM(a.used_pages)  * 8 / 1024 AS used_mb,    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 AS unused_mbFROM sys.tables tJOIN sys.schemas s        ON t.schema_id = s.schema_idJOIN sys.indexes i        ON t.object_id = i.object_idJOIN sys.partitions p    ON i.object_id = p.object_id AND i.index_id = p.index_idJOIN sys.allocation_units a ON p.partition_id = a.container_idGROUP BY s.name, t.nameORDER BY total_mb DESC;

• 多张推送/同步相关大表存在：

• unused_mb 占比高

• 表内碎片严重

• 但由于业务限制：

• ❌ 不能锁表

• ❌ 不能做 ALTER INDEX … REBUILD

四、已执行的数据清理动作

1️⃣ 按业务要求，仅保留 3 个月历史数据

执行存储过程（示例）：

EXEC dbo.usp_BigTable_Cleanup_NoBatch    @TableName='****',    @DateColumn='CreatedDate',    @KeepMonths=3;

✅ 结果：

• 表数据量显著下降

• 但 mdf 文件大小不变（符合预期）

五、数据库空间回收处理（不锁表方案）

1️⃣ 回收策略说明

在以下约束条件下执行：

• ❌ 不锁表

• ❌ 不做索引 REBUILD

• ✅ 允许短时间 I/O 抖动

• ✅ 目标为 “有限但安全的空间回收”

2️⃣ 使用 DBCC SHRINKFILE 回收数据文件

第一次试探性回收（目标 390GB）

USE xxx;GODBCC SHRINKFILE (N'xxx', 399360);  -- 390GB

Shrink 后验证

SELECTname,size * 8 / 1024 / 1024 AS size_gbFROM sys.database_files;

结果（操作后）：

✅ 数据文件成功释放 约 37GB

六、事务日志文件异常增长处理

1️⃣ 日志增长原因说明

• Shrink 数据文件过程中：

• 大量页移动

• 产生大量事务日志

• 属于 正常现象

2️⃣ 日志处理步骤

（1）确认恢复模式


SELECT    name,    recovery_model_descFROM sys.databasesWHERE name ='xxx';

（2）FULL 模式下先执行日志备份

BACKUP LOG xxxTODISK='E:\SQLBAK\xxxx_log_20260128.trn'WITH INIT, COMPRESSION;

（3）确认日志可回收状态

DBCC SQLPERF(LOGSPACE);

（4）收缩日志文件至合理大小

USE xxx;GODBCC SHRINKFILE (N'xxx_log', 20480);  -- 20GB

（5）重新设置日志增长策略（防止再次异常膨胀）

ALTER DATABASE xxxMODIFY FILE (    NAME = N'xxx_log',    SIZE = 20480MB,    FILEGROWTH = 4096MB);

七、风险控制与取舍说明（重要）

• 本次操作 未执行索引 REBUILD

• 已知风险：

• 索引碎片上升

• 查询性能可能轻微下降

• 风险接受前提：

• 不锁表、不停机

• 以“应急释放磁盘空间”为目标

后续建议：

在合适维护窗口内，仅对 1~2 张最大表 执行
ALTER INDEX … REBUILD
再进行一次 shrink，可进一步释放空间。

八、最终处理结果总结

• 数据库数据文件：

• 427GB → 390GB

• 日志文件：

• 已通过日志备份 + shrink 恢复至合理范围

• 历史数据清理策略：

• 已明确为 仅保留 3 个月

• 风险：

• 可控

• 不影响业务连续性

阅读原文：原文链接