SQL Server数据库瘦身实战：搞定日志文件与数据文件空间膨胀

做了这么多年数据库运维我几乎每个月都能遇到一两次“数据库快把磁盘撑爆了”的求救。客户说某业务库已经膨胀到几百GB备份耗时越来越长磁盘空间告警邮件天天半夜弹出来但一问实际业务数据量往往连一半都不到。这种“虚胖”基本都是同一个套路日志文件失控、索引碎片堆积、过期数据没有归档、文件空间管理混乱。这篇文章就把我在 Sql Server 数据库瘦身这件事上踩过的坑、验证过的方法、以及可以直接抄作业的脚本一次讲清楚适合 DBA、后端开发和运维同学参考。1. 为什么数据库会越长越“胖”瘦身的前提是先搞清楚体积是从哪里涨起来的。Sql Server 的磁盘占用主要落在两个文件上数据文件.mdf / .ndf和日志文件.ldf。这俩膨胀的机制完全不同处理方式也截然不同混为一谈是新手最容易犯的错误。1.1 数据文件膨胀的常见来源数据文件体积大不代表“数据真的多”更常见的是数据页里有大量空洞。举个生活化的例子你的 C 盘里有个 Excel 文件里面删掉了几万行数据但 Excel 文件本身还是那么大。数据库也一样DELETE 或 UPDATE 操作只是把数据页里的行标记为“可重用”但不会自动把空间还给操作系统。日积月累文件就虚胖了。数据文件膨胀还有几个隐形推手频繁 DELETE 大量数据后没有收缩数据页空了大半。索引碎片严重索引页被拆得七零八落为了装入同样多的索引条目占用了更多页。堆表Heap Table在频繁删除后空间碎片化严重页面密度极低。开启了某些功能后额外产生内部版本记录例如查询用了较长时间的运行事务快照隔离。没有任何过期数据清理策略历史数据不断堆积也没有滚动归档。需要注意数据文件一旦设置了自动增长AUTOGROWTH每次增长都会申请新的空间而 Sql Server 不会自动把空出来的页归还给操作系统。所以“文件很大”和“空间利用率高”是两回事这个区别整个瘦身过程都要记住。1.2 日志文件膨胀的特殊性日志文件膨胀的原因跟数据文件完全不是一回事。事务日志LDF记录的是所有修改操作的完整前映像和后映像它的大小取决于两个因素产生了多少日志量以及这些日志空间是否被“截断重用”。很多数据库被配置成“完整恢复模式FULL”但从未配置过定期的事务日志备份。完整恢复模式下日志备份是唯一能让日志空间被截断重用的手段。只要日志备份没成功或者日志备份链路断了日志文件就会一直增长哪怕业务量不大一次大的批量 UPDATE 就能把日志撑出几百GB。更麻烦的是即使日志内部已经截断重用了日志文件的物理大小同样不会自动缩小。这跟数据文件是一个道理你删掉了日志内部的内容但不主动执行 SHRINKFILE文件在磁盘上依然占着几百GB。所以要瘦身必须把“逻辑收缩”和“物理文件缩小”两个动作配合起来做。2. 瘦身前必做的全面体检不管接手哪个库第一步永远不是直接动手收缩而是先回答三个问题数据文件和日志文件分别多大、里面实际用了多少空间、哪些索引和表在浪费空间。没有这个体检结果后面所有操作都是盲目的。2.1 文件大小与空间使用率检查先看整个库的文件分布和可用空间。下面这段脚本在任何一个版本上都适用SELECT DB_NAME(database_id) AS db_name, type_desc AS file_type, name AS logical_name, size * 8 / 1024 AS size_mb, FILEPROPERTY(name, SpaceUsed) * 8 / 1024 AS used_mb, CASE WHEN max_size -1 THEN -1 ELSE max_size * 8 / 1024 END AS max_size_mb, growth * 8 / 1024 AS growth_mb FROM sys.master_files WHERE database_id DB_ID();这个脚本直接显示了当前库各个文件的逻辑名、总大小、实际使用大小和增长步长。如果 “size_mb” 远大于 “used_mb”恭喜你有巨大的收缩空间如果两者接近那说明文件确实是“真胖”问题出在数据量本身或索引碎片上。数据文件内部的空闲空间还可以精确到页分配类型级别。用下面的动态管理视图可以看得更细SELECT type_desc, total_pages * 8 / 1024 AS total_mb, allocated_pages * 8 / 1024 AS allocated_mb, unallocated_pages * 8 / 1024 AS unallocated_mb FROM sys.dm_db_file_space_usage WHERE database_id DB_ID();这段脚本的价值在于把“未分配的页”单独列出来也就是整个文件里还完全没有被任何对象使用的空间。这一数字直接决定了你能收缩出多少物理空间。2.2 索引碎片与表空间排行索引碎片是数据库“虚胖”的重要来源。用系统自带的动态管理函数扫描当前库所有索引SELECT OBJECT_NAME(ps.object_id) AS table_name, i.name AS index_name, ps.index_type_desc, ps.avg_fragmentation_in_percent AS fragment_pct, ps.page_count, ps.avg_page_space_used_in_percent AS page_space_used_pct FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, SAMPLED) ps JOIN sys.indexes i ON ps.object_id i.object_id AND ps.index_id i.index_id WHERE ps.page_count 1000 ORDER BY ps.avg_fragmentation_in_percent DESC;注意最后加了个过滤条件只查 page_count 大于 1000 的索引。碎片率对“只有几页的索引”没有意义重点看大表和频繁增删改的索引。如果 avg_fragmentation_in_percent 超过 30%同时 avg_page_space_used_in_percent 低于 70%这个索引就是瘦身首要目标。表级别的空间占用排行同样重要快速定位哪几张表吃了大部分空间SELECT OBJECT_NAME(ps.object_id) AS table_name, SUM(ps.reserved_page_count) * 8 / 1024 AS reserved_mb, SUM(ps.used_page_count) * 8 / 1024 AS used_mb FROM sys.dm_db_partition_stats ps GROUP BY ps.object_id ORDER BY reserved_mb DESC;一般跑一遍下来就能确定瘦身方案重灾区在哪日志文件还是数据文件数据文件里是大表碎片还是历史数据堆积一目了然。3. 文件收缩的正确打开方式体检完成之后才进入“瘦身”的核心动作收缩文件。这部分内容网上一搜一大把但很多人直接把 SHRINKDATABASE 整个库一收然后抱怨性能变差、文件又涨回去。问题出在没用对工具和流程。3.1 SHRINKDATABASE 与 SHRINKFILE 怎么选Sql Server 提供两个命令DBCC SHRINKDATABASE对整个库的所有文件统一收缩自动跳过没有收缩价值的文件。DBCC SHRINKFILE针对单个逻辑文件按指定目标大小收缩。我的经验是优先用 SHRINKFILE一条一条地来。SHRINKDATABASE 的问题是粒度太大你不能控制“只收缩日志文件”或者“数据文件收缩到某个具体目标值”。生产环境里你往往只想处理那个失控的日志文件而数据文件收缩后会产生大量碎片动它需要更谨慎。SHRINKFILE 可以精确控制收缩目标还能用 TRUNCATEONLY 参数只释放未使用空间不搬运数据页对数据文件的压力最小。3.2 数据文件与日志文件的收缩步骤收缩日志文件的标准流程我建议按照下面的顺序操作先看一下日志逻辑名和当前恢复模式。然后执行一次事务日志备份如果你处于完整恢复模式让日志内部完成截断。接着收缩目标文件设置一个理想的目标大小。-- 1. 查看当前恢复模式和日志逻辑名 SELECT name, recovery_model_desc FROM sys.databases WHERE database_id DB_ID(); -- 2. 完整恢复模式下先做日志备份截断日志 BACKUP LOG [YourDB] TO DISK ND:\Backup\YourDB_LogBackup.trn; -- 3. 收缩日志文件到目标大小 1GB USE [YourDB]; DBCC SHRINKFILE (NYourDB_log, 1024);如果日志文件里面还有大量“活动的”VLF虚拟日志文件没有截断SHRINKFILE 会收缩失败或只能缩到某个卡点。这时先重复执行日志备份或者把恢复模式短暂切到 SIMPLE 让它完成截断再切回 FULL。生产环境切换恢复模式前务必备份完整数据库。数据文件收缩要更谨慎。假如数据文件的实际已使用空间是 180GB物理文件 500GB那目标大小设成 250GB 比设成 185GB 明智得多。理由很简单目标设得越低收缩时 Sql Server 需要搬移的数据页越多IO 压力越大操作耗时越长碎片也越严重。收缩的最终目标不该是“刚好塞满”而是留出合理的增长余量。-- 收缩数据文件到目标大小 250GB ALTER DATABASE [YourDB] SET RECOVERY SIMPLE; DBCC SHRINKFILE (NYourDB, 256000); ALTER DATABASE [YourDB] SET RECOVERY FULL;注意上面这段在切换恢复模式前必须先有完整备份。SIMPLE 模式下做收缩只是为了绕开日志截断链路收缩完成后必须立即恢复 FULL 模式然后补一次完整备份确保备份链完整。3.3 收缩的代价与频率控制收缩不是免费的。SHRINKFILE 在移动数据页时会把页面从文件末尾搬到文件前面这个过程必然导致索引碎片急剧上升。收缩完成后紧接着做一次索引碎片整理是刚需操作不是可选操作。另外收缩操作本身会消耗大量 IO 和 CPU选在业务低谷期做同时监控数据库的等待类型。如果 PAGEIOLATCH 等待飙升说明收缩挤占了正常业务读写的 IO必须降低收缩速度或错峰。还有一条铁律不要频繁收缩。数据库维护计划里千万别挂一个“每天收缩一次”的定时任务。频繁收缩会造成索引碎片反复出现也让文件增长机制反复触发既伤性能又伤磁盘寿命。收缩只应该作为“一次性的补救动作”而不是“日常维护动作”。4. 从根上治理索引碎片整理与无效索引清理很多人只知道收缩文件忽略了索引维护。文件收缩只是把“虚胖”减掉但如果索引碎片不处理过不了一个月数据库又会涨回来因为碎片让页面利用率持续走低数据文件只能靠自动增长扩容来兜底。4.1 碎片阈值怎么定索引碎片整理有两条路重组REORGANIZE和重建REBUILD。选哪条路看体检数据。碎片率在 5%~30% 之间用 ALTER INDEX ... REORGANIZE也就是在线重组锁需求低IO 压力小。碎片率超过 30%用 ALTER INDEX ... REBUILD直接重建整个索引。如果索引很大考虑使用 ONLINEON 参数企业版支持。碎片率低于 5% 且页面填充率正常不需要处理动了反而浪费 IO。我通常关注的除了碎片率还有 avg_page_space_used_in_percent。一个索引碎片率只有 8%但页面填充率只有 40%说明页面里全是空隙。这种情况重组效果有限重建顺便把页密度提上来才是瘦身的关键收益。给一个批量生成维护脚本的示例SELECT ALTER INDEX [ i.name ] ON [ OBJECT_NAME(ps.object_id) ] CASE WHEN ps.avg_fragmentation_in_percent 30 THEN REBUILD WITH (ONLINE ON, SORT_IN_TEMPDB ON) ELSE REORGANIZE END ; AS maintenance_script FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, SAMPLED) ps JOIN sys.indexes i ON ps.object_id i.object_id AND ps.index_id i.index_id WHERE ps.page_count 1000 AND ps.avg_fragmentation_in_percent 5 ORDER BY ps.avg_fragmentation_in_percent DESC;这一段会直接输出所有需要维护索引的可执行语句。大索引重建建议加 SORT_IN_TEMPDBON意思是排序操作放到 tempdb 里做能显著减少源数据库的日志压力。4.2 那些“看不见”的空间浪费除了碎片整理更该做的是“删索引”。不少业务系统上线后经历了大量功能迭代索引也东加一个西加一个很多索引压根没人用。索引一多数据文件自然膨胀因为每个索引都有自己的 B 树页面。查看索引真实使用情况可以用这个脚本SELECT OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name, i.type_desc, s.user_seeks s.user_scans s.user_lookups AS total_reads, s.user_updates AS total_writes FROM sys.dm_db_index_usage_stats s JOIN sys.indexes i ON s.object_id i.object_id AND s.index_id i.index_id WHERE s.database_id DB_ID() ORDER BY total_reads ASC;如果某个索引的 total_reads 是 0而 user_updates 很高说明这个索引除了拖慢写入、浪费空间之外没有任何价值。删之前先和业务确认确认没有定期查询用到它再 DROP。这一招能释放出来的空间有时比整个收缩操作还多。4.3 填充因子Fill Factor不要乱调有人为了“防止碎片”把填充因子调成 50%结果索引页面撑不满一半数据文件反而多占了一倍空间。填充因子是控制索引页预留多少空闲空间的参数适合的值大多在 80%~90% 之间。对于读写比高的业务默认 0相当于 100%反而最省空间但更新频繁的索引可以设 85% 左右减少页分裂。调填充因子后必须重建索引才会生效并且它会直接增加数据文件占用瘦身时不要为了“防碎片”而牺牲空间得不偿失。5. 数据清理与归档真正的长效瘦身方案如果数据文件的“胖”是因为历史数据堆了几年没动过那么收缩和索引维护都只是治标。真正的瘦身方案是建立数据生命周期管理哪些数据留在线库、哪些数据进归档库、哪些数据直接销毁要形成一套规则。5.1 分批 DELETE 与日志截断配合一次性 DELETE 几千万行历史数据会导致事务日志瞬间爆炸还可能撑爆临时空间。正确做法是分批删除每批删除一定数量后提交一次事务。DECLARE batch_size INT 10000; DECLARE rows_affected INT 1; WHILE rows_affected 0 BEGIN DELETE TOP (batch_size) FROM Orders WHERE OrderDate 2020-01-01; SET rows_affected ROWCOUNT; CHECKPOINT; END这里的 CHECKPOINT 是为了确保脏页写入磁盘释放部分日志空间。在完整恢复模式下每批删除后也应该配合事务日志备份或定期截断不然你删了 1 亿条数据日志文件照样能涨到天上去。分批 DELETE 的批量大小要根据你的硬件和日志文件大小来调整业务高峰期宁可调小一点也别让事务日志失控。5.2 分区表与归档库的取舍历史数据按时间归档最优雅的方案是分区表滑动窗口。把月表数据按月份分区用分区切换SWITCH把过期分区整块移出到归档库几秒就能完成一个月的归档操作几乎不产生日志压力。这个方案适合“数据量大、按时间查询频繁、需要在线保留最近 N 年”的业务场景。分区切换的具体做法是通过 ALTER TABLE ... SWITCH PARTITION 语法把当前生产库某个分区切换到归档表或归档库。切换完成后生产库不再保留该分区数据文件空间会显著释放。这里有个细节归档表和源表结构必须完全一致而且索引和约束也要对齐否则 SWITCH 操作会报错。如果没有条件改造成分区表也可以直接在两个库之间用 SSIS 或者 SQL Agent 作业定期搬运数据搬完删除生产库老数据。这种方式实现简单缺点是删除过程会产生大量日志、搬运期间业务表会锁。各有利弊看你的维护窗口和团队能力选择合适的方案。5.3 小心 TRUNCATE 和 DELETE 的区别清理数据时新手容易把 TRUNCATE TABLE 当成 DELETE 的“效率版”但两者差别非常大。TRUNCATE 只记录页释放信息几乎不产生事务日志同时也无法按条件过滤——整个表的数据一次性清空。DELETE 可以带 WHERE 条件每删除一行都会记日志删除量的日志甚至比数据本身还大。如果你只想清空一张表且确认不需要回滚TRUNCATE 确实香。但生产环境往往有外键约束有 CDC变更数据捕获之类机制在监听TRUNCATE 在这些场景下可能是被禁止的。还有一点DELETE 之后可以做空间收缩TRUNCATE 之后表的物理空间也已经立即标记为可重用但文件同样不会自动变小仍然需要 SHRINKFILE 配合。6. 日志文件长期可控的维护机制日志文件是数据库超空间问题的“重灾区”但很多人处理完日志文件就以为大功告成了结果下个季度日志又膨胀一次。说到底日志的瘦身不是“事后补救”而是“长效机制”。6.1 恢复模式决定日志空间能否重用首先要跟你负责的库确认一件事它处于什么恢复模式SIMPLE 模式自动截断日志但只支持上一次完整备份后的恢复存在数据丢失风险。FULL 模式必须依赖事务日志备份链完成截断否则日志只增不减。BULK_LOGGED 模式部分操作最小化日志适合批量导入场景但仍需日志备份截断。很多系统的数据库被设置成 FULL 模式是因为当初建库的人默认选了“完整”但后续没人配置日志备份。正确做法是对每个 FULL 模式的库必须规划事务日志备份作业频率至少是每 30 分钟一次业务高峰期可以加密到 15 分钟。日志备份间隔越长单个日志备份文件越大崩溃恢复时丢失的数据也越多。SIMPLE 模式不是“不产生日志”而是“日志空间被重用得快”。如果一个库允许 RPO数据恢复点目标大于 0可以接受最近若干分钟的损失那么 SIMPLE 模式既省心又省空间。但把生产核心库切成 SIMPLE 之前一定要想清楚恢复策略。6.2 日志备份失败的常见排查点日志文件膨胀往往和“日志备份一直失败”叠加出现。备份失败常见原因也就那么几个备份目标磁盘满了日志备份写不进去。备份作业的账号没有足够权限。作业计划设置错了只在周末备份而业务天天在产生日志。备份完成后没有校验备份链断了但没人发现。我用过最土也最有效的方案是给日志备份作业配上失败告警。一旦日志备份失败第一时间收到邮件而不是等磁盘快满了才发现。日志文件一旦失控瘦身流程虽然能救回来但风险远远高于事前拦截。7. 瘦身实操中的高频问题与避坑记录最后这部分整理下我实际做过的多个瘦身项目里反复遇到的坑。这些坑几乎每个团队都会踩一遍说明书上不会写记录下来比任何理论都有用。7.1 数据文件收缩为什么特别慢有一次某业务系统数据文件 1.2TB实际数据只有 400GBSHRINKFILE 跑了 9 个小时还没结束。排查发现文件里有一个巨大的堆表碎片率高达 60%扫描和搬移数据页的 IO 压力远远超过预期。后来学到的经验数据文件收缩前先针对碎片严重的表做一次索引重建或重组。先把页面填满收缩需要搬移的页数量会大幅度下降总耗时反而比直接收缩少得多。这属于“先整理再装箱”比直接搬运整个文件高效得多。另外 SHRINKFILE 如果长时间没有进度输出不代表卡死了它本来就是单线程后台操作。用系统视图监控收缩进度时观察文件的 used_pages 是否持续下降。如果数值在动就安心等着别中途杀掉因为中断后下次还要从头再来。7.2 收缩后性能不升反降的原因上次给某库做完瘦身第二天开发反馈几个报表查询变慢了。查了一下问题就出在收缩时移动了大量数据页导致聚簇索引碎片率从 12% 飙到 55%。文件收缩确实释放了磁盘空间但查询性能被拖垮了。这件事给我的教训是文件收缩和索引维护必须放在同一个维护窗口里执行顺序是“先收缩、后重建索引”。收缩会打乱所有数据页的物理顺序重建索引负责把页重新排列把碎片率降回去。顺序反了或者只做一半等于白干一半。7.3 日志文件的“缩了又涨”死循环有人每个月执行一次日志收缩日志缩到 1GB然后半个月后回到 200GB。这类反复膨胀几乎都是恢复模式是 FULL 但日志备份没跟上。日志收缩只是把物理文件缩小日志备份才是让日志内部空间持续重用的根本。这种情况的正确解法是检查日志备份作业是否存在并正常运行恢复模式是否合适最后再谈收缩。如果日志备份作业已经稳定运行日志文件自然维持在一个稳定水位根本不需要每个月手动收缩。日志文件膨胀是“结果”日志备份缺失才是“原因”。只解决结果不解决原因瘦身就是打地鼠游戏。7.4 磁盘空间不够日志备份做不了日志备份目的地满了会导致日志备份失败进而让日志文件越长越大最后陷入死循环。常规处理是在磁盘还剩余几GB 时立刻清理备份目录或挂载新磁盘。买不起新磁盘的紧急项目也可以把日志备份临时改到网络共享路径先把截断链路恢复起来再做后续清理。还有一招在紧急情况下可以把备份压缩开起来BACKUP LOG ... WITH COMPRESSION日志备份体积通常能缩小一个数量级。8. 关于数据库瘦身的最后一点经验数据库瘦身这件事我认为最重要的是“换个视角看问题”压缩文件大小只是手段不是目标。目标应该是让数据文件、日志文件都维持在一个稳定的、可预测的水位空间利用率合理备份耗时可控故障恢复路径清晰。文件收缩永远是对过去管理失误的补救不是日常应该频繁执行的动作。如果把这个项目比作一次大扫除那么正确的工作量配比大概是这样的日志机制修正占一半历史数据归档占三成文件收缩和索引维护占两成。每次做数据库瘦身项目我都会先把“根因”这条线画清楚——日志备份有没有、恢复模式对不对、历史数据要不要留、索引有没有冗余。这四个问题有了答案剩下的无非是执行时间和风险控制。最后一个小技巧送给大家做完整个瘦身项目之后建一个每周执行的“数据库空间体检”作业输出文件使用率、日志状态、碎片率、TOP 表空间报表发到 DBA 邮箱或者运维群。只要这个例行体检在跑任何数据库从“虚胖”走向“真胖”的过程都会在早期被发现。几十行脚本换来的是以后再也不用半夜爬起来救急。这点小投入是我做数据库维护多年里觉得最划算的一笔投资。