1. 事故现场先看现象再谈原理这件事发生在一个周三的上午大概十点半刚过我正在处理另一个项目的需求变更运维同事的电话就进来了生产环境的订单服务挂了用户提交订单一直转圈超时之后直接弹出系统异常页面。我第一反应是看应用服务毕竟大部分“程序崩溃”最后都能归结到代码bug或连接池耗尽。但这次不太一样因为从监控上看应用进程还活着CPU和内存也没异常真正的问题是数据库端的连接全部被拒应用日志里刷满了“无法打开登录所请求的数据库”之类的内容。项目标题里的关键词是“SQL Server 2008 数据库事务日志已满”但实际排查过程并不是一上来就看到这个结论的。我先看了Windows事件日志发现SQL Server错误日志里有大量类似“transaction log for database xxx is full due to ACTIVE_TRANSACTION”的记录这条信息基本就是问题定位的起点。也就是说程序端看到的“崩溃”其实是数据库无法接受新事务、已有事务无法提交导致应用请求全部阻塞最终触发了连接池异常和请求超时。这里要强调一个容易被忽略的事实SQL Server 2008已经是一个非常老旧的版本但它至今仍跑在不少企业的生产环境里。老版本的问题在于很多新版本里自动化的诊断手段用不上很多排查工作得靠手工SQL和错误日志来完成。这篇文章就是要把我这次从程序异常、数据库告警、日志分析到最终处理完成的完整链路写出来给还在维护SQL Server 2008的同学一份可以直接参考的排查清单。2. 排查过程从应用日志到数据库日志的完整链路2.1 应用日志的异常堆栈里藏着第一层线索我先拉取了目标服务的应用日志因为程序崩溃的第一现场一定会在日志里留下异常堆栈。正常情况下如果是代码层面的空指针、参数错误堆栈会直接定位到某某Service层或者Controller层。但这次满屏都是数据库连接相关的错误比较典型的几条如下“Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.”“This may have failed because the server is not configured to allow remote connections.”“A transport-level error has occurred when receiving the result from the server.”这些信息看起来像是网络问题或连接串配置问题但它们有一个共同的前提应用从连接池申请数据库连接时数据库端已经无法正常分配资源。此时不能急着改代码而是要立刻确认数据库本身是否还健康。我做了几个快速检查服务器CPU和内存使用率、磁盘剩余空间、数据库是否能正常执行简单查询。结果发现CPU正常、内存正常但存放数据库文件的D盘可用空间只剩不到200MBSQL Server的错误日志里也开始出现磁盘空间不足的提示。到这里问题已经缩小到了磁盘或日志层面但还不能确定就是事务日志满因为系统数据库和用户数据库共用同一个磁盘时任何库的日志膨胀都可能把磁盘塞满。2.2 SQL Server错误日志与系统视图交叉定位确认数据库能连上之后我立刻执行了下面这两条SQL一条看整体日志空间使用情况一条看当前数据库的日志复用等待状态DBCC SQLPERF(LOGSPACE); GO SELECT name, log_reuse_wait_desc, is_in_standby, recovery_model_desc FROM sys.databases; GODBCC SQLPERF(LOGSPACE)会返回所有数据库的日志文件大小、已用空间百分比和状态。当时看到的结果是主业务库的日志文件总大小虽然显示只有8GB但日志已用空间百分比已经超过了99%基本上就是“日志文件已被事务填满无法继续写入新日志记录”的状态。而在log_reuse_wait_desc这一列里我看到的值是ACTIVE_TRANSACTION。这个值非常关键。SQL Server的事务日志是一个循环复用的结构当日志不再被需要时可以被截断并复用。但如果存在未提交的事务哪怕磁盘上还有空间日志也无法截断因为数据库还需要保留这些日志用于事务回滚或崩溃恢复。ACTIVE_TRANSACTION直接表明有一个长事务从某个时间点开始就一直没提交也没回滚它牢牢挡住了日志复用的位置。接下来需要找到这个事务是谁。下面这条命令可以查看到当前最旧的活动事务信息DBCC OPENTRAN(数据库名称); GO返回结果里我看到了一个事务的开始时间大约是当天上午九点四十分左右。也就是说九点四十有人开启了一个事务一直没提交到十点半左右已经把日志完全写满。这段时间里所有需要写日志的正常业务事务全部被卡住应用端的表现自然就是“假死”。2.3 从业务代码里找到那个“不提交”的事务有了事务开始时间下一步就是从应用侧排查当时在跑的作业。我们系统的后台有一个定时任务每隔半小时同步一次历史数据到统计库代码里用的是SqlTransaction但同步完成后的Commit()调用被放在了某个统一处理方法的末尾一旦中间某个批次的数据出现异常代码会直接return导致事务对象被丢在那里既不提交也不回滚。这类问题在开发环境很难暴露因为数据量小日志增长慢事务就算挂几十分钟也未必写满日志文件。但生产环境的数据量是开发环境的几十倍一个长时间未提交的事务会不断累积日志记录最终把事务日志文件和磁盘空间全部吃掉。所以整个排查链路其实是这样的程序崩溃表象 → 连接超时异常 → 数据库连接拒绝 → 磁盘空间不足 → 事务日志已满 → 找到活动事务 → 定位到应用代码未提交事务。每一步之间都有因果关系缺一个环节都可能把排查方向带偏。3. 根因剖析SQL Server 2008事务日志机制与日志满的底层逻辑3.1 事务日志到底存的是什么为什么会影响整个数据库很多开发同学会把事务日志简单理解成“操作记录”或者“备份用的东西”这个理解不够准确。SQL Server的日志文件通常命名为xxx_log.ldf记录的是每一个事务对数据库所做的修改包括插入、更新、删除操作的前映像和后映像。它的核心作用是保证ACID属性具体来说有两个场景最重要崩溃恢复数据库实例意外宕机时SQL Server重启后要借助日志把未完成的事务回滚、把已提交但尚未写入数据文件的事务重做。事务回滚应用执行了ROLLBACK时数据库需要根据日志把数据恢复到事务开始之前的状态。所以日志文件一旦满了数据库不单是“不能写新日志”那么简单而是连已有事务都无法继续推进。因为任何一个事务的后续操作都需要写日志写入失败则事务被迫中止大量连接开始报错程序自然崩溃。日志满还会导致数据库进入只读模式此时应用连读操作都可能失败因为有些查询也需要申请日志空间来维护系统内部的一致性。再补充一点SQL Server 2008的日志文件在创建时有两个重要参数初始大小和自动增长。如果初始大小设置得过小自动增长步长又不够大那么日志文件会频繁触发增长操作但如果设置得太大比如一次增长2GB遇到磁盘空间不足时又会增长失败。这次事故里的业务库日志文件其实已经自动增长到了8GB但8GB里99%以上都是没法复用的活动事务日志这就等于日志文件再大也无济于事。3.2 为什么一个未提交事务会在半小时内把日志塞满我们可以做个粗略估算。假设业务库中有一张大表定时任务每批更新10000行数据每天执行几十次更新一行记录的日志量大约在200到400字节之间加上事务头、锁信息等额外开销一批事务产生的日志量可能在3MB到6MB左右。如果这个事务不提交后续每新增一批数据日志文件就会继续膨胀因为SQL Server无法截断任何早期日志。半小时内从正常状态飙升到99%说明这个未提交事务在持续执行大量的写入操作。当时那个同步任务确实一直在循环更新数据每一轮循环都往日志里追加内容。更麻烦的是因为事务没有提交SQL Server为了支持潜在的回滚操作必须把这些日志全部保留于是日志文件只能不断增长。当磁盘剩余空间不够时日志增长失败数据库立即进入类似“只读”的异常状态。这里有一个非常容易踩的坑很多人以为日志文件满了之后只要删掉磁盘上的其他文件腾出空间数据库就会自动恢复。实际上不是这样。日志文件能否复用取决于日志中是否存在ACTIVE_TRANSACTION而不是磁盘上有没有空间。只要那个长事务还存在哪怕你给它腾出100GB空间日志仍然会被认为“满”状态因为SQL Server的逻辑判断是“日志中包含需要保留的活动记录当前文件无法循环复用”。3.3 日志满之后程序崩溃的传导机制把程序崩溃和日志满这两件事串起来是这次排查里最有价值的部分。程序的崩溃不是SQL Server主动把连接杀掉而是所有请求在等待数据库响应最终超过应用自己的超时阈值后抛异常。具体传导过程是这样的应用从连接池取出一个连接向SQL Server发送一个新事务的开始请求。SQL Server尝试为新事务写入日志记录发现日志文件已满且无法增长。SQL Server给应用返回错误代码9002“The transaction log for database xxx is full”。应用收到错误后通常会把该连接标记为不可用并从连接池中移除。随着失败连接越来越多连接池被不断创建新连接、再失败、再移除陷入了恶性循环。高并发下最终所有请求都在等待连接池释放连接出现“Timeout expired”异常。所以程序端的现象可能会让你误以为是连接池配置太小或数据库网络不通但真正的源头其实只有一句话日志文件满了数据库不再接受新事务。理解了这条传导链后续的应急处理和长期预防才会有明确的方向。4. 应急处置在不停机的前提下恢复服务4.1 先确认恢复模式再决定能不能直接截断日志真正动手处理之前先查一下恢复模式这一步非常关键因为完整恢复模式FULL和简单恢复模式SIMPLE下日志处理的合法手段完全不同。当时我执行了SELECT recovery_model_desc FROM sys.databases WHERE name 数据库名称;结果返回的是FULL。这意味着按照正规流程应该先做一次日志备份日志备份本身就会截断日志中不再需要的部分然后再收缩日志文件。但在生产环境故障恢复的紧急状态下如果磁盘空间已经岌岌可危很多时候无法再执行一个完整的日志备份因为备份操作本身也会消耗空间。这里我必须说明生产环境日常运维中正规的处理顺序永远是“先日志备份、再收缩”。但在应急抢修的极限情况下如果日志备份已经无法完成且业务影响面持续扩大可以选择将数据库临时切换为简单恢复模式收缩日志文件然后再切回完整恢复模式并立即做一次完整备份。这种做法会破坏日志链导致无法恢复到切换点之后的时间点所以只适用于业务可以接受一定数据丢失的故障场景。当时我和业务方确认后也确实走了这条应急路径执行语句如下ALTER DATABASE [数据库名称] SET RECOVERY SIMPLE; GO DBCC SHRINKFILE(N数据库名称_log, 1); GO ALTER DATABASE [数据库名称] SET RECOVERY FULL; GODBCC SHRINKFILE里的第二个参数1代表目标大小是1MB这只是为了尽快释放空间。实际上日志文件收缩后还会随着业务运行再次增长所以这一步只是把磁盘空间还回来真正的长期解决方案在后面的监控和预防里。4.2 不同场景下的日志截断方案怎么选不是所有场景都适合上面这套“切简单模式再切回来”的思路尤其是对一些数据一致性要求极高的系统比如金融或电商核心链路破坏日志链是不可接受的。更稳妥的方案是尽量不切换恢复模式而是分步操作第一步找到并终止那个未提交事务。可以通过DBCC OPENTRAN拿到事务的SPID然后和应用负责人确认是否可以KILL这个会话。杀掉会话后事务会回滚日志中相应的活动记录会被释放。第二步执行一次日志备份让日志截断发生。第三步再执行DBCC SHRINKFILE收缩日志文件释放磁盘空间。这个方案的好处是保持了日志链的完整性坏处是执行时间取决于那个事务的大小。如果事务已经运行了一个小时回滚本身也需要很长时间期间数据库还是会因为日志满而拒绝新事务。所以实践中的选择逻辑很简单等不及回滚且业务能接受数据丢失就走简单模式应急等得起回滚且业务严格要求数据完整性就Kill会话并做日志备份。还有一种情况是磁盘空间没有完全耗尽日志文件本身还有一点余量此时可以先加一个日志文件或增大现有日志文件的自动增长上限给数据库留出继续工作的空间然后再从容处理未提交事务。当时我们已经连执行查询都困难了增容这条路来不及走只能应急处理。4.3 收缩完成后验证程序恢复情况的几个步骤日志收缩完成后我先执行了最简单的一条验证语句确认数据库状态恢复正常SELECT DATABASEPROPERTYEX(数据库名称, Status) AS DBStatus;返回ONLINE之后我又执行了下面几条检查确保不会在同一个坑里再摔一次查看日志文件当前大小DBCC SQLPERF(LOGSPACE);确认已用百分比下降到正常水平。查看log_reuse_wait_desc确认不再是ACTIVE_TRANSACTION。用SELECT * FROM sys.dm_exec_requests WHERE session_id 50查看当前是否有大量阻塞会话。应用侧我让运维同事重启了对应服务的连接池其实就是重启一下应用进程因为连接池里可能还残留着之前被标记为不可用的连接。重启之后用户下单流程恢复正常请求响应时间也回到了几十毫秒级别。到这一步事故的应急处理算是完成了但真正的麻烦是如果没有后续预防同样的故障最多一个月就能再发生一次。5. 长期预防把事务日志纳入日常监控体系5.1 恢复模式选型完整模式要配日志备份简单模式要接受时间点丢失这次事故暴露出一个很典型的管理问题业务库采用了完整恢复模式但从未配置过任何日志备份任务。完整恢复模式的默认设计思路是日志文件增长到一定程度后通过定期日志备份把不再需要的日志截断腾出空间。日志备份一旦缺失日志文件就只能无限增长直至撑爆磁盘。这和“只买车不保养等到抛锚了再骂车质量差”是一个道理。如果你所在的系统对数据恢复时间点要求不高比如可以接受最近几分钟到几小时的数据丢失那么直接把恢复模式设置为简单模式也是一种合理选择。简单模式下日志会在检查点发生时自动截断不需要额外的日志备份任务日志文件体积也能保持相对稳定。它的代价是你无法做时间点恢复只能恢复到最近一次完整备份或差异备份的时间点。选哪种恢复模式取决于业务需求而不是数据库管理员个人的偏好。金融类系统通常必须用完整模式因为监管要求数据可以恢复到任意时间点内部管理系统、报表系统用简单模式反而省心得多。这里最容易犯的错误是明明是内部系统数据丢了也能接受却偏偏用了完整恢复模式然后又没人做日志备份结果日志无限膨胀隔几个月就来一次“磁盘满了”的报警。5.2 日志备份与维护计划的正确配置方法如果业务要求保留完整恢复模式那么日志备份就是一条不可妥协的底线。SQL Server 2008里可以通过维护计划来配置定期日志备份也可以直接用SQL脚本配合Windows任务计划程序。我更推荐脚本方式因为维护计划在SQL Server 2008上偶尔会出现版本升级后无法打开编辑器的兼容性问题而脚本不受这个限制。下面是当时我配置的日志备份脚本每小时执行一次DECLARE backupPath NVARCHAR(256); SET backupPath ND:\Backup\YourDB_Log_ REPLACE(CONVERT(VARCHAR(20), GETDATE(), 120), :, _) .trn; BACKUP LOG [数据库名称] TO DISK backupPath WITH NOFORMAT, NOINIT, NAME NYourDB-日志备份; GO脚本保存为.sql文件然后用sqlcmd配合Windows任务计划程序调用即可。备份文件建议保留最近48小时超期的自动清理避免备份文件把磁盘再堵一次。备份频率方面事务量大的系统建议15到30分钟一次事务量小的系统可以放宽到每小时或每天但不要超过24小时否则日志文件仍然可能增长过大。5.3 监控与告警把风险控制在用户发现之前SQL Server 2008没有内置的云监控能力但它提供了性能监视器计数器可以通过Windows性能监视器Perfmon采集关键指标也可以写一个简单的轮询脚本来监控日志使用率。我自己常用的脚本是这样的每五分钟执行一次并检查日志使用率是否超过80%CREATE TABLE #LogSpace ( DatabaseName NVARCHAR(128), LogSizeMB DECIMAL(18, 2), LogUsedPercent DECIMAL(18, 2), Status INT ); INSERT INTO #LogSpace EXEC(DBCC SQLPERF(LOGSPACE)); SELECT DatabaseName, LogSizeMB, LogUsedPercent FROM #LogSpace WHERE LogUsedPercent 80; DROP TABLE #LogSpace;如果返回结果有记录说明有数据库日志使用率超过80%需要人工介入。把这个脚本放进计划任务配合一个简单的邮件通知或企业微信通知基本能做到日志膨胀的提前预警。另一个需要关注的是log_reuse_wait_desc列它如果长期显示LOG_BACKUP说明日志备份任务可能已经停止运行。长期预防里还有一个容易被忽略的点应用代码中长事务的控制。这次事故的直接触发者就是代码里那个不提交的事务。在项目规范里应该明确规定数据库事务必须在一个方法内完成提交或回滚杜绝跨越远程调用、循环体、甚至是用户交互过程的事务。一旦事务持续时间超过设定阈值就应该触发告警这样等不到日志满问题就已经暴露了。6. 常见问题速查与避坑记录这段时间里我陆续处理过几次不同表现形式的日志满问题下面这些场景和判断方法可以作为快速参考。常见现象可能原因快速判断方法处理手段应用报连接超时数据库CPU却不高日志满或活动事务阻塞查DBCC SQLPERF(LOGSPACE)和log_reuse_wait_desc截断日志或处理活动事务数据库无法写入报9002错误事务日志满无法自动增长查看错误日志中的9002记录日志备份收缩或切换简单模式应急log_reuse_wait_desc显示ACTIVE_TRANSACTION存在未提交事务执行DBCC OPENTRAN查看最早事务联系应用负责人Kill会话log_reuse_wait_desc显示LOG_BACKUP日志备份任务未运行检查维护计划和备份历史配置定期日志备份日志文件很大但已用百分比很低日志文件设置得太大或收缩不彻底查看文件大小和已用空间DBCC SHRINKFILE收缩并设置合理初始大小手动执行DBCC SHRINKFILE却不释放空间日志尾部还有活动日志检查是否有长时间运行的事务或备份操作先处理事务/等待备份完成再收缩这里要特别说明一个关于DBCC SHRINKFILE的误区。网上很多教程让你直接执行DBCC SHRINKFILE(日志文件名, 目标大小)来给日志瘦身但实际上在完整恢复模式下如果不先做日志备份或切换恢复模式紧缩操作通常不会生效太久甚至完全无法收缩。原因就是日志中还存在大量未被标记为可复用的逻辑日志段。正确的姿势是先让日志截断发生再收缩物理文件。简单模式或日志备份之后DBCC SHRINKFILE才会真正把文件尺寸降下来。另外一个坑是关于日志初始大小设置。SQL Server 2008的日志文件如果设置为“自动增长”增长方式支持按MB和按百分比两种。按百分比增长看似灵活但在大文件场景下会产生日志碎片而且每次自动增长都会造成短暂的I/O停顿。建议把日志初始大小设置得接近日常峰值自动增长步长设置成一个固定值比如512MB或1GB减少频繁增长的概率。这项设置属于“平时不显山露水关键时刻能救命”的类型。最后再分享一个小技巧排查这类问题时尽量保留一套SQL Server 2008的测试环境专门用来验证日志截断、备份恢复和收缩操作。生产环境里手抖一下的代价太大了测试环境里随便折腾踩完了坑再上生产能省掉很多不必要的惊吓。这次事故之后我们把所有生产库的日志使用率监控、日志备份任务和事务执行时间告警都补齐了。至少到现在没有再出现过因为日志满了导致的程序崩溃。希望这篇排查记录能给你一些参考遇到类似问题时心里更有底。