作者：桦仔
10余年DBA工作经验
微信：debolop
QQ交流群：740052625
公众号：数据库实战派
最近帮助北京某医院优化数据库空间管理的案例，从磁盘报警的原因追踪到问题解决的过程，如何通过数据库维护策略避免类似问题的再次发生。
事件回顾：磁盘报警，找不到原因？
9月26日中午12点到9月27日下午6点，北京某医院的信息科收到了一条紧急报警：磁盘空间不足。由于医院的日常运作高度依赖信息化系统，任何一个数据库的异常都可能影响整个业务流程。医院信息科负责人立即展开调查，但在初步排查后，仍无法确定是哪一个数据库、哪一张表导致了数据文件的异常暴涨。
为避免系统瘫痪，医院信息科迅速对磁盘进行了紧急扩容。然而，问题的根源仍然没有找到。医院信息科负责人通过某种方式联系到我，让我协助他们排查磁盘空间占用暴涨的问题。
用户数据库版本是：SQL Server2016
深入排查：数据文件暴涨的幕后黑手
在进一步深入排查后，我们发现暴涨的数据文件位于 trancenter 数据库中，该数据库在短短一天半的时间内增加了150GB的数据量。此时，阿里云控制台的监控日志提供了一条重要线索：一个定时作业可能是导致数据文件暴涨的原因。
快速查看所有数据库的大小和未使用的空间，初步确定是哪一个数据库导致了数据暴涨。
SELECT
name AS DatabaseName,
size * 8 / 1024 AS SizeMB,
size * 8 / 1024 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) * 8 / 1024 AS UnusedSpaceMB
FROM
sys.master_files
WHERE type = 0 -- 数据文件类型（不包括日志文件）
ORDER BY SizeMB DESC;
这个定时作业每隔一段时间会将大量历史数据写入 trancenter 数据库中的一张表，随后通过 DROP TABLE 操作清理掉历史数据。然而，问题在于：虽然 DROP TABLE 删除了表，但 SQL Server 并不会立刻释放相应的物理空间，导致数据库的文件尺寸持续膨胀。
确定具体是哪些表占用了大量空间：
USE trancenter;
SELECT
t.name AS TableName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB,
SUM(a.used_pages) * 8 / 1024 AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 AS UnusedSpaceMB
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
GROUP BY
t.name, p.rows
ORDER BY
TotalSpaceMB DESC;
问题解决：合理利用数据库空间收缩
既然问题的原因已经明确，接下来就需要找到解决方法。由于数据库的文件尺寸暴增后并未自动收缩，导致空间得不到及时释放，最终引发磁盘告急。因此，我们决定使用 数据库文件收缩操作，手动释放那些已经被删除但未归还操作系统的空间。
通过如下的 SQL 脚本，成功将 trancenter 数据库中800GB的可用空间压缩释放，系统空间瞬间恢复正常。
-- 收缩指定的数据库文件
DBCC SHRINKFILE (YourDataFileName, target_size_in_MB);
这次操作解决了磁盘空间不足的问题。
经验总结：如何防止类似问题再次发生？
尽管此次问题得以解决，但要避免未来再出现类似情况，这里给出一些针对性的优化建议和长期维护策略。
优化定时作业：减少空间占用 使用 TRUNCATE TABLE 替代 DROP TABLE 在数据删除过程中，如果不需要删除表结构，建议使用 TRUNCATE TABLE，它比 DROP TABLE 更高效
引入表分区机制 对于大量历史数据的管理，建议使用表分区。通过将数据按时间分区，可以定期清理老旧分区，避免直接删除整个表，减少空间浪费。
数据库空间监控：提前预防 设置空间使用预警 建议对数据库的文件增长趋势进行监控，设置告警阈值。例如，当数据库文件的可用空间即将耗尽时，提前触发告警，防止系统因空间不足而崩溃。
定期巡检与维护 除了依靠告警机制，定期巡检数据库文件大小及可用空间情况也是非常必要的。通过定期检查，能够及时发现空间异常增大的情况，并通过如 DBCC SHRINKFILE 等方式手动收缩文件，避免磁盘空间再次告急。
合理使用空间收缩：注意碎片问题 收缩操作虽然能快速释放空间，但要注意其可能会导致数据库内部的碎片化问题，进而影响性能。因此，在执行收缩操作后，建议使用 ALTER INDEX REBUILD 或 DBCC INDEXDEFRAG 重新整理索引，减少碎片对查询性能的影响。
长期优化策略：向更高效的数据库管理迈进
随着医院信息化的发展，数据的快速增长将成为长期挑战。因此，除了临时解决方法，数据库的设计和维护策略还需要更长期的优化。
数据库归档 对于历史数据量大的业务，建议采用数据库归档策略，将不再频繁使用的历史数据迁移到单独的归档数据库中。这样可以避免生产数据库的快速膨胀，提高性能。
使用压缩技术 对于增长较快的大表，可以启用 SQL Server 的数据压缩功能，减少存储需求（行压缩（ROW Compression）、列存储索引压缩（Columnstore Compression）、页压缩（PAGE Compression））。
总结
在信息化高度发达的医疗行业，数据库的高效管理与维护是保障业务持续稳定运行的关键。通过此次案例，我们解决了北京某医院数据库空间暴涨的问题，还为未来的数据库维护提出了合理建议。希望这些经验能够为其他医疗机构提供参考，帮助大家避免类似的数据库管理问题。
加入我们的微信群，与我们一起探讨数据库技术，以及SQL Server、 MySQL、PostgreSQL、MongoDB、Redis、Oracle 的相关话题。
微信群仅供学习交流使用，没有任何广告或商业活动。
