如何移除SQL Server中未关联表的已分配页以缩减MDF文件？

一、修正查询，定位真正的孤立分配页 #

你当前的查询使用INNER JOIN会过滤掉没有对应对象的分配页，导致无法看到真正的未关联页（你看到的NULL行是总计行）。请改用以下查询定位孤立页：

USE [你的目标数据库名]; -- 替换为实际数据库名称，不要用temp_db
SELECT 
    sch.[name] AS 架构名,
    obj.[name] AS 表名,
    ISNULL(obj.[type_desc], N'孤立分配页') AS 对象类型,
    COUNT(*) AS 预留页数,
    (COUNT(*) * 8) AS 预留KB,
    (COUNT(*) * 8) / 1024.0 AS 预留MB,
    (COUNT(*) * 8) / 1024.0 / 1024.0 AS 预留GB
FROM sys.dm_db_database_page_allocations(DB_ID(), NULL, NULL, NULL, DEFAULT) pa
LEFT JOIN sys.all_objects obj
    ON obj.[object_id] = pa.[object_id]
LEFT JOIN sys.schemas sch
    ON sch.[schema_id] = obj.[schema_id]
WHERE obj.[object_id] IS NULL -- 筛选无对应对象的孤立页
GROUP BY sch.[name], obj.[name], obj.[type_desc]
ORDER BY 预留页数 DESC;

二、确认孤立页来源 #

这类孤立页通常来自：

• 删除表后未彻底清理的残留页（如未提交事务、快照隔离的版本数据）
• 批量操作、索引重建等遗留的临时分配页
• 数据库损坏（极少数情况）

先执行以下命令排除数据库损坏：

DBCC CHECKDB([你的目标数据库名]) WITH NO_INFOMSGS, ALL_ERRORMSGS;

三、清理孤立页并缩减MDF文件 #

1. 收缩数据文件（谨慎使用）

如果孤立页是可释放的空白空间，可先收缩文件释放末尾空白（TRUNCATEONLY不会移动数据，无碎片风险）：

USE [你的目标数据库名];
-- 替换为实际数据文件名，可通过sys.master_files查询文件名
DBCC SHRINKFILE(N'你的数据文件名', TRUNCATEONLY);

若末尾无空白，需使用不带TRUNCATEONLY的命令移动数据，但会产生索引碎片，之后需重建索引：

DBCC SHRINKFILE(N'你的数据文件名', 目标大小MB); -- 指定收缩后的目标大小

2. 清理版本存储（若使用快照隔离/读提交快照）

若孤立页来自版本存储，先查看版本存储大小：

SELECT name, SUM(size)*8/1024 AS 大小MB FROM sys.master_files WHERE database_id=DB_ID() GROUP BY name;
SELECT * FROM sys.dm_tran_version_store;

结束长时间运行的事务可触发版本存储自动回收，也可调整版本存储的回收阈值。

3. 重建/整理索引

对剩余大表重建索引，可将分散的空白页集中到文件末尾，便于后续收缩：

-- 对单个表重建所有索引，支持在线操作的版本添加ONLINE=ON避免锁表
ALTER INDEX ALL ON [data].[crm_isc_sales_work_oppor_1b22e] REBUILD WITH (ONLINE = ON);

4. 清理可变长度列残留页

若孤立页来自删除表后遗留的可变长度/大对象列，使用以下命令清理：

DBCC CLEANTABLE([你的目标数据库名], N'data.crm_isc_sales_work_oppor_1b22e') WITH NO_INFOMSGS;

四、后续预防措施 #

• 大表删除改用分批操作，避免一次性产生大量空白页
• 定期维护索引，减少碎片堆积
• 设置合理的数据库自动增长阈值，避免文件一次性过度扩容

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