mssql数据库磁盘空间占用分析及压缩

精确空间占用，并按总预留空间从大到小排序：
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
USE westzdc; — 请确认数据库名正确

SELECT
SCHEMA_NAME(t.schema_id) AS [架构],
t.name AS [表名],
— 行数
SUM(p.rows) AS [行数],
— 总预留空间 (KB) -> 这是排序依据
SUM(a.total_pages) * 8 AS [总预留空间 KB],
— 总预留空间 (MB)
SUM(a.total_pages) * 8 / 1024 AS [总预留空间 MB],
— 数据空间 (KB)
SUM(CASE WHEN p.index_id IN (0, 1) THEN a.data_pages ELSE 0 END) * 8 AS [数据空间 KB],
— 索引空间 (KB)
(SUM(a.used_pages) – SUM(CASE WHEN p.index_id IN (0, 1) THEN a.data_pages ELSE 0 END)) * 8 AS [索引空间 KB],
— 未使用空间 (KB) —— 这就是可以释放的部分
(SUM(a.total_pages) – SUM(a.used_pages)) * 8 AS [未使用空间 KB]
FROM
sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.is_ms_shipped = 0 — 排除系统表
AND i.type <= 1 — 堆或聚集索引
GROUP BY
t.schema_id, t.name
ORDER BY
SUM(a.total_pages) DESC; — 👈 按总占用从大到小排

立即执行这个诊断查询.查询详细占用
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
USE westzdc;

— 1. 查看数据库整体空间分配情况
SELECT
SUM(total_pages) * 8 / 1024 AS [总分配空间 MB],
SUM(used_pages) * 8 / 1024 AS [已使用空间 MB],
(SUM(total_pages) – SUM(used_pages)) * 8 / 1024 AS [未使用空间 MB]
FROM sys.allocation_units;

— 2. 查看各类对象的空间占用（包括系统表、索引等）
SELECT
OBJECT_SCHEMA_NAME(p.object_id) AS [架构],
OBJECT_NAME(p.object_id) AS [对象名],
p.index_id,
SUM(a.total_pages) * 8 AS [占用 KB],
CASE
WHEN p.object_id IS NULL THEN ‘系统表/内部对象’
WHEN p.index_id = 0 THEN ‘堆表数据’
WHEN p.index_id = 1 THEN ‘聚集索引’
ELSE ‘非聚集索引’
END AS [对象类型]
FROM sys.partitions p
JOIN sys.allocation_units a ON p.partition_id = a.container_id
GROUP BY p.object_id, p.index_id
ORDER BY SUM(a.total_pages) DESC;

收缩数据库
~~~~~~~~~~~~~~~~~~~~~~~

DBCC SHRINKFILE (N’westzdc_Data’, 24);

重建索引并收缩
~~~~~~~~~~~~~~~~~~~~~~~~~~

USE westzdc;

— 步骤1：重建所有用户表的索引（释放表内碎片）
EXEC sp_MSforeachtable @command1 = “ALTER INDEX ALL ON ? REBUILD”;

— 步骤2：重建系统表的索引（需要高级权限，可选）
— DBCC DBREINDEX(‘sys.sysrowsets’); — 一般不手动操作

— 步骤3：更新所有空间使用统计
EXEC sp_spaceused @updateusage = ‘true’;

— 步骤4：查看更新后的空间情况
EXEC sp_spaceused;

— 步骤5：如果上面步骤后总分配空间仍远大于表占用，执行收缩
DBCC SHRINKDATABASE(westzdc, 10); — 收缩到留10%空闲空间