接手这套库的时候我第一眼看到的就是那个 12GB 的日志文件而数据文件本身连 2GB 都不到。数据库明明是 SIMPLE 恢复模式日志却“发福”成这个样子不用猜也知道历史原因多半是曾经在 FULL 模式下长期没做日志备份或者是有人手动改过恢复模式但没按流程走。看完日志空间的使用率我反而松了口气因为 12GB 的量里实际用掉的可能连 1% 都不到纯粹是物理文件撑得很大“瘦身”的难度不大真正麻烦的是后面那些恢复模式、截断和收缩之间的逻辑关系。这篇内容我把当时的完整处理过程和踩过的坑梳理出来希望能帮同样被事务日志撑爆磁盘的人少走点弯路。1. 先搞清楚日志为什么“发福”1.1 一个必须被反复讲解的底层机制很多 DBA 刚接触 SQL Server 时会把“事务日志膨胀”和“数据量变大”划等号实际上事务日志记录的不是“数据快照”而是每次修改操作的顺序记录。简单说它像一本流水账每一条 INSERT、UPDATE、DELETE、DDL 都会在日志里留存一条记录事务提交后这些记录的主要用途是保证崩溃恢复。事务日志的写入是“循环覆盖”的理论上日志文件应当在旧记录不再需要时重用空间。但“不再需要”有一个非常严格的定义它取决于下面几个因素是否允许截断检查点是否发生是否已执行日志备份FULL / BULK_LOGGED 模式是否存在长时间未提交的事务是否存在数据库镜像、可用性组、日志传送等复制链路是否存在延迟的某个活动事务只要有一个因素把日志的“活跃头部”卡住后面的 VLF虚拟日志文件就无法被复用。这么多层机制叠加在一起日志文件就会只增不减12GB 甚至 100GB 都只是时间问题。1.2 恢复模式与日志“截断”到底是什么关系恢复模式有三个选项SIMPLE、FULL、BULK_LOGGED。很多人只是死记“FULL 支持时间点恢复SIMPLE 不支持”但落到日志空间管理上这两个模式的行为差异才是真正影响磁盘占用和运维策略的关键。在SIMPLE 模式下检查点会自动截断事务日志中不再需要的部分所以这个模式下的事务日志天然就不会无限膨胀。这里的“截断”不是让文件变小而是把日志中已不再需要的 VLF 标记为可重用文件大小保持不变但里面的“空间”被释放出来了。数据库引擎会在后台自动做不需要运维人员频繁干预。在FULL 模式下事情就完全不同了。为了保证任意时间点恢复日志链必须完整保存下来所以检查点不会主动截断事务日志。只有执行BACKUP LOG日志备份之后日志中已备份的部分才会被标记为可重用。换句话说在 FULL 模式下如果日志备份缺失日志文件会一直增长直到把磁盘塞满。还有个容易忽略的细节数据库镜像和可用性组要求强制 FULL 恢复模式。在这些环境下哪怕你切换到 SIMPLE操作也会被系统拒绝或产生严重后果所以处理前必须确认实例环境。1.3 从 12GB 到 500MB 的本质很多人一开始把“12GB 瘦身到 500MB”理解成一个“压缩日志文件”的操作其实这个理解并不准确。我们要做的事情本质上是两步让日志中的“活跃部分”缩短到最小也就是让旧的、不再需要的日志记录被截断。物理收缩日志文件把文件末尾不用的空间归还给操作系统。如果第一步不做任何DBCC SHRINKFILE都只会得到一句“警告文件占用空间不足”因为日志文件内部有大量 VLF 仍被标记为不可复用物理收缩自然无从谈起。这也是为什么我强烈建议在动手前先把日志空间的使用率查清楚——用DBCC SQLPERF(LOGSPACE)看 Log Space Used(%)如果这个值只有不到 1%说明绝大多数空间都已经处于可回收状态只是文件物理大小没有缩回来而已。接下来要做的事就完全变成“如何把 VLF 让位 如何让文件收缩”的技术活了。2. 动手前要摸清的三个关键事实2.1 弄清楚当前恢复模式这个步骤看起来简单但很多事故恰恰发生在这里。我的习惯是先用一条语句把所有数据库的恢复模式全部查出来SELECT name AS DatabaseName, recovery_model_desc AS RecoveryModel FROM sys.databases;如果发现目标数据库是 FULL 恢复模式先不要急着切到 SIMPLE而是先回忆一下这个库在业务中的定位是否需要分钟级恢复是否有可用性组绑定是否在日志传送链路中如果这些前置条件没有确认清楚后面的切换就可能带来数据完整性风险。比较稳妥的做法是先查询数据库上的同步复制配置SELECT database_id, is_encrypted, is_ledger_on FROM sys.databases WHERE name 某业务库;如果实例上有可用性组还要通过sys.dm_hadr_database_replica_states查看复制状态确认当前处理方式不会影响同步链路。2.2 量一量日志文件到底装了什么在决定收缩方案前先查日志文件的物理大小和增长设置SELECT DB_NAME(database_id) AS DatabaseName, name AS LogicalName, physical_name AS FilePath, size / 128.0 AS FileSizeMB, max_size AS MaxSize, growth / 128.0 AS GrowthByMB, is_percent_growth FROM sys.master_files WHERE database_id DB_ID(某业务库);注意size列的单位是“页”1 页等于 8KB所以size/128.0是 MB。这条语句能帮你快速定位日志文件逻辑名称后面DBCC SHRINKFILE会用到。接着用系统命令直接看日志空间利用率DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS;输出里有一列Log Space Used (%)如果数字低于 5%说明日志文件内部堆积的主要是可以重用的碎片物理收缩完全可行。如果这个数字很高比如 80%那说明日志里确实积压了大量活跃内容先别想着收缩而是要找出是谁占住了日志。查找长时间活动事务SELECT session_id, transaction_id, database_transaction_log_bytes_used, database_transaction_log_bytes_reserved FROM sys.dm_tran_database_transactions WHERE database_id DB_ID(某业务库);如果发现有会话持有事务超过几十分钟甚至几小时那就必须先处理这个事务否则后面无论怎么切换恢复模式日志的“头部”都固定在某个位置VLF 无法让位收缩也会失败。2.3 确认业务允许的“断开”窗口这一步太容易被跳过但恰恰是最容易引发事故的一环。把数据库从 FULL 切到 SIMPLE意味着在这个窗口内发生的数据变更无法通过日志备份来做时间点恢复如果恰好此时出现误删数据那基本上只能依赖完整备份或差异备份来恢复损失可能达到数小时。所以我在操作之前一定会做三件事找一个业务低峰期至少空出 30 分钟到 1 小时的维护窗口。提前做一次完整备份确保有可以回滚的基线。和业务方口头确认这个时间窗口内不做关键事务操作。如果这是一个核心交易库无法接受任何恢复窗口那就不要用“切到 SIMPLE 再切回来”这种粗暴方式正确方法是先保持 FULL做完整备份连续做日志备份到很小再使用 DBCC SHRINKFILE 每次收缩一点。虽然慢但风险最低。3. “瘦身”实战从 12GB 压到 500MB 的标准流程3.1 先做好必须的安全垫底操作我在处理某个模拟项目X的数据库时步骤是从完整备份开始的因为后面每一步都有可能出问题手边有一份备份文件心里就不慌。BACKUP DATABASE 某业务库 TO DISK D:\SQLBackup\某业务库_FULL_20250115.bak WITH INIT, COMPRESSION, STATS 10; GO这条命令里INIT是覆盖同路径同名文件COMPRESSION能显著减小备份体积STATS 10会在每完成 10% 时输出进度方便长时间备份时确认任务没卡住。备份完成后再确认一下恢复模式如果当前是 FULL就可以顺手在切到 SIMPLE 之前做一个日志备份把当前日志链完整保留下来万一后面想按时间点恢复至少能用上这最后一个日志备份。BACKUP LOG 某业务库 TO DISK D:\SQLBackup\某业务库_LOG_beforeSwitch.trn WITH INIT, COMPRESSION; GO3.2 利用 SIMPLE 模式配合检查点完成截断备份就绪后切换到 SIMPLE 恢复模式并主动发出一个检查点让日志中所有已提交事务记录的“活跃”部分立刻进入截断状态。USE master; GO ALTER DATABASE 某业务库 SET RECOVERY SIMPLE; GO CHECKPOINT; GO在我处理的那个案例里执行完这两条命令后再跑一次DBCC SQLPERF(LOGSPACE)看到Log Space Used (%)直接从 60% 掉到了接近 0%。这里有两个细节值得注意第一条ALTER DATABASE是在master上下文中执行的不要在目标库的上下文里执行切换操作避免出现库级连接占用导致切换失败。CHECKPOINT通常可以省略因为 SIMPLE 模式本身会自动触发检查点但手动加一个是让截断动作尽快发生收缩前心里更有底。如果你是在 FULL 模式下直接尝试DBCC SHRINKFILE(某业务库_log, 512)则很可能只有警告且不生效。所以必须先切 SIMPLE 或者先执行日志备份让日志截断后才谈得上收缩。3.3 用 DBCC SHRINKFILE 逐步收缩文件现在到了核心收缩环节。日志文件逻辑名称从前面sys.master_files查出来的结果里拿例如某业务库_log。目标大小是 500MB我就先给DBCC SHRINKFILE指定一个 500 的值单位默认是 MB。USE 某业务库; GO DBCC SHRINKFILE(N某业务库_log, 500); GO注意DBCC SHRINKFILE的第二个参数不能小于当前日志文件中“最小大小”这个最小大小通常由最初创建文件时的大小决定。如果日志文件从创建起就一直是 12GB那么要想一次收缩到 500MB失败的可能性比较大因为它要先等日志内部收缩到可以容纳 500MB 的状态但如果最小大小高于 500MB就会直接拒绝。为了解决这个问题我的习惯是分阶段收缩每次给一个当前大小 50% 左右的目标USE 某业务库; GO DBCC SHRINKFILE(N某业务库_log, 6000); GO DBCC SHRINKFILE(N某业务库_log, 3000); GO DBCC SHRINKFILE(N某业务库_log, 1500); GO DBCC SHRINKFILE(N某业务库_log, 700); GO DBCC SHRINKFILE(N某业务库_log, 500); GO每执行一次后再跑一次DBCC SQLPERF(LOGSPACE)观察使用率并配合sys.master_files看文件实际大小是否有变化。通常前几次物理大小会明显下降到 1GB 以下会有些吃力因为 SQL Server 收缩日志文件的逻辑受日志链和 VLF 分布影响只要当前活跃 LSN 不在文件末尾收缩就能继续直到遇到活跃头部为止。我在实际处理中还遇到过那种“外部轮转日志”的情况就是收缩到一定程度后文件大小一直停在 3GB 不动查询下来发现日志文件创建时自动增长被设置为 8MB 的百分比增长导致 VLF 被切成非常碎的小段活跃 VLF 散布在文件中部和末尾。这种情况下我会先缩小增长幅度把文件收缩到一个较整齐的状态比如收缩到 1GB然后重新设置增长单位为固定 MB减少后续碎片化。3.4 切回 FULL 之后绝不能漏掉的两件事日志文件收缩到 500MB 之后重启任务才完成了一半。接下来把恢复模式切回 FULLUSE master; GO ALTER DATABASE 某业务库 SET RECOVERY FULL; GO此时最容易犯的第一个错误是忘记做完整备份。在 FULL 恢复模式下日志链必须在完整备份或差异备份之后才能建立如果切回 FULL 后直接开始日志备份SQL Server 会立刻报错错误信息类似“数据库没有可用于日志备份的备份基线”或者日志备份任务直接失败。所以切回 FULL 后的第一件事就是马上做一个完整备份BACKUP DATABASE 某业务库 TO DISK D:\SQLBackup\某业务库_FULL_afterSwitch.bak WITH INIT, COMPRESSION, STATS 10; GO第二件不能漏掉的事是立刻开启常规的事务日志备份。如果你不把定时日志备份配置好这次 12GB 瘦身到 500MB 的成果很快就会被打破也许 3 天、也许 5 天日志又会重新膨胀回几个 GB。为什么是“几个 GB”因为 FULL 模式下每一次未被日志备份覆盖的变更都会堆积绝不是只看数据文件大小就能判断出来的。这里给一个可以直接落到作业里的测试命令BACKUP LOG 某业务库 TO DISK D:\SQLBackup\某业务库_LOG_afterSwitch_001.trn WITH INIT, COMPRESSION; GO如果这条命令执行成功说明日志链已经建立后面就可以交给维护计划或者 Agent 作业去定时执行。4. 那些年我们踩过的日志坑4.1 切回 FULL 后日志备份立刻失败这是我在多个项目里遇到过的高频问题典型场景是有人发现日志太大就把恢复模式改成 SIMPLE等日志缩小后又改回 FULL然后就开始做日志备份结果报错。根本原因是从 SIMPLE 切到 FULL 之后数据库的“日志备份链”处于断裂状态。要恢复这条链必须重新建立一个“备份基线”也就是完整备份或差异备份。这和安全备份策略的逻辑一致系统不允许你对一个没有基线的库做增量日志备份。排查方法很简单执行SELECT db.name, db.recovery_model_desc, db.is_in_standby, db.is_cleanly_shutdown FROM sys.databases db WHERE db.name 某业务库;然后看备份历史SELECT s.database_name, s.type, s.backup_start_date, s.backup_finish_date, s.first_lsn, s.last_lsn, s.backup_set_id FROM msdb.dbo.backupset s WHERE s.database_name 某业务库 ORDER BY s.backup_start_date DESC;如果type D的完整备份在切换之后不存在那就说明问题出在“缺基线”上。补一个完整备份日志备份立刻恢复。4.2 SIMPLE 模式下日志文件还是物理很大还有人会遇到更让人头疼的场景数据库明明已经是 SIMPLE 模式日志文件还是占用 5GB、10GB使用率也确实很低但DBCC SHRINKFILE就是收缩不下去。这种情况大多不是恢复模式导致的而是一下几个原因实例上配置了数据库镜像或 AlwaysOn 可用性组日志被强制定位为“活跃”不能截断必须和辅助副本同步。此时单纯切 SIMPLE 反而会破坏整体架构需要顺着可用性组/镜像的机制来处理。存在一个非常老的未提交事务把日志头锚定在一个很长的 LSN 位置。事务持续时间可能已经超过几天得在sys.dm_tran_database_transactions里找出对应的 session并最终结束该事务。日志文件自身被预先分配了很大的物理大小虽然内部已截断但文件末尾的空间被分配给了活动 VLF导致无法收缩。这种情况下需要多次执行DBCC SHRINKFILE每次收缩一点并检查警告信息。数据库上有 “损坏页” 标记或LOG_REUSE_WAIT_DESC非空SQL Server 出于一致性考虑不允许立即收缩。一个万能的排查语句SELECT db.name, db.recovery_model_desc, db.log_reuse_wait_desc FROM sys.databases db WHERE db.name 某业务库;log_reuse_wait_desc的值直接告诉你日志空间不能复用是因为什么。如果是REPLICATION说明复制代理还没消费完日志如果是ACTIVE_TRANSACTION说明有长事务如果是AVAILABILITY_REPLICA说明是在等可用性组辅助副本如果是NOTHING那就说明理论上已经可以收缩了大概率是文件尾部 VLF 结构问题。4.3 收缩完没几天又涨回 12GB这恐怕是最常见的运维笑话费了老大力气收缩到 500MB一周后日志又涨到 8GB。这不是系统有毛病而是维护策略缺失导致的必然结果。根本原因如果数据库是 FULL 恢复模式但没有配置日志备份日志会一直增长。很多系统的完整备份只做每日一次甚至每周一次日志备份基本没有那事务日志就只能原地自我膨胀。解决办法很朴素在完整备份之外给日志备份排一个高频作业。在容量足够的前提下日志备份频率可以设为每 30 分钟一次对于核心交易库甚至可以做到每 5–10 分钟一次。空间充足时日志文件会在备份后自动重用物理文件不会缩小但内部空间始终不会耗尽这比反复收缩健康多了。另一个因素是“自动增长步调设置过大”。如果日志文件的growth设置为 512MB 甚至 1GB 的百分比增长那么一次大事务就会把文件撑爆到 12GB之后即使日志备份按时完成物理文件也不会自动收缩。所以我在制定维护策略时会把日志文件的增长单位改成固定大小的 MB例如 64MB、128MB并且设置最大上限避免失控。4.4 可用性组/复制环境里的特例在 AlwaysOn 可用性组环境里日志收缩的复杂度会明显提升。首先你必须保持主副本为 FULL 恢复模式所以“切到 SIMPLE 再切回来”这条路基本行不通。其次日志截断不仅取决于主副本的日志备份还取决于辅助副本同步进度。如果某个辅助副本卡住或会话断开主副本的日志就无法截断日志文件会持续增长。应对方式不是切模式而是检查可用性组数据同步状态。检查sys.dm_hadr_database_replica_states中的redo_queue_size和log_send_queue_size。先修复同步或删除异常副本再执行日志备份。在主副本上做日志备份后规规矩矩用DBCC SHRINKFILE一点点收缩。日志传送和事务复制环境也存在类似约束代理任务必须能够成功消费日志否则log_reuse_wait_desc会卡在REPLICATION或LOG_BACKUP。遇到复制环境我每次都会先确认所有下游链路都在健康状态再动手处理日志。5. 比“瘦身”更重要的日常养护5.1 建立日志备份节奏日志收缩这件事实在是“治标不治本”。要长期维持日志不爆核心看日志备份是否到位。以下是我在实际运维中会用到的简单节奏可以根据业务恢复点目标RPO调整业务场景日志备份频率完整备份频率核心交易系统 / 核心结算库5~10 分钟一次每日一次或每日多次普通业务系统30 分钟一次每日一次开发测试库4~8 小时一次每周一次数据仓库 / 报表库每日一次每周一次不要觉得日志备份频繁会增加系统压力实际上日志备份非常轻量它只是把日志中已备好的 VLF 标记为可重用并写入一小份备份文件对磁盘和 CPU 的影响远低于一次完整备份。日志备份失败的预警一定要监控起来。常见错误码是 9002代表事务日志已满也意味着数据库可能即将变成只读影响会很严重。5.2 给日志文件定一个合理的“初始身材”日志文件频繁增长和收缩和 VLF 碎片高度相关。日志文件如果从 100MB 自动增长到 12GB中间可能会创建成百上千个大小不一的 VLF这就是为什么收缩后文件会变得难以驯服。更好的做法是预估业务高峰时段的日志写入量给日志文件设置一个合理的初始大小。以某核心库为例业务方告知每小时事务产生大约 300MB 日志同时每半小时会做一次日志备份那我就会把日志文件初始大小设为 1GB自动增长设为 128MB。这样日常运行中很少触发文件扩展VLF 数量稳定日志文件和 DBA 都能保持情绪稳定。修改日志文件初始大小可以用DBCC SHRINKFILE收缩到目标也可以在创建数据库时直接指定CREATE DATABASE [某新库] ON PRIMARY ( NAME N某新库, FILENAME ND:\SQLData\某新库.mdf, SIZE 2GB ) LOG ON ( NAME N某新库_log, FILENAME ND:\SQLLog\某新库_log.ldf, SIZE 1GB, FILEGROWTH 128MB ); GO生产库已经存在的可以这样调整增长步调避免一次涨太多ALTER DATABASE 某业务库 MODIFY FILE ( NAME 某业务库_log, SIZE 1GB, FILEGROWTH 128MB, MAXSIZE 8GB ); GO5.3 监控与告警配置日志空间监控不应等到 12GB 报 9002 才后知后觉。我常用的监控脚本会查询每个数据库的当前日志空间使用率当超过 80% 时产生告警。比如DECLARE threshold DECIMAL(5, 2) 80.0; SELECT db.name AS DatabaseName, log_usage.UsedPercent, mf.size / 128.0 AS CurrentSizeMB FROM sys.databases db JOIN sys.master_files mf ON db.database_id mf.database_id AND mf.type 1 CROSS APPLY ( SELECT CONVERT(DECIMAL(10, 2), ((SELECT SUM(used_pages / 128.0) FROM sys.dm_db_file_space_usage WHERE database_id db.database_id)) / NULLIF(mf.size / 128.0, 0) * 100) AS UsedPercent ) log_usage WHERE mf.type 1 AND log_usage.UsedPercent threshold ORDER BY log_usage.UsedPercent DESC;这类语句放到 SQL Server Agent 作业里每 5 分钟跑一次超过阈值就发送通知基本不会出现日志涨到 12GB 才发现的情况。5.4 什么时候可以物理收缩什么时候千万别碰物理收缩日志文件本身不是“罪”但确实是一种高代价操作它会阻塞部分 I/O同时还可能引起 VLF 碎片化。我的经验是一次性清理历史遗留的大日志文件适合收缩。日常日志管理靠日志备份和预分配就够了不要频繁收缩。生产环境、运行中数据库除非紧急磁盘空间问题否则尽量在维护窗口收缩。在 AlwaysOn、镜像、日志传送、事务复制环境中收缩前先确保所有副本/下游链路同步完成。收缩完成后立刻做日志备份确认日志链完整。每次收缩前我都习惯再问自己一遍这个日志到底是因为该备份没备份还是真的业务量大如果是前者切到 SIMPLE 再 shrink 只能是救急真正的问题还在原地等你。写在最后的一点经验处理这类日志膨胀问题我最大的体会是成功的“瘦身”并不是从 12GB 变成 500MB 那个瞬间而是切回 FULL 之后日志备份机制是否真正落地。如果你没有把后续的日志备份计划配置完整那这次的收缩成果很可能只是替下次的“日志爆仓”争取一点时间而已。另外再提一个小技巧收缩日志文件时尽量在维护窗口把AUTO_SHRINK关掉并检查一下VLF数量和文件增长配置。很多“收缩后还是很大”或者“收缩完又涨回来”的问题根源都在文件自动增长设置得太大、太随意。把这个问题和日志备份频率一起解决掉日志文件才会真正安静下来。希望这次分享能把恢复模式、日志截断、物理收缩这几件事串成一个完整的链条。以后你再看到某个库的日志文件异常巨大至少能先判断出是哪一环出了问题再用对工具和流程去处理。