tempdb日志文件暴增分析

合集 - SQL Server数据库运维(59)

1.SQL SERVER日常运维巡检系列之-日志2024-12-302.关于SQL Server镜像的一个小误区2016-07-023.一切从“简”，解放IT运维人员2016-12-164.CPU问题定位与解决2017-02-075.数据库优化案例——————某市中心医院HIS系统2017-01-186.系统隐形杀手——阻塞与等待2017-01-117.SQL Server常见问题介绍及快速解决建议2016-12-20

8.tempdb日志文件暴增分析2022-11-09

9.数据库服务器CPU不能全部利用原因分析2022-11-0710.如何快速定位TempDB产生问题2017-02-2811.索引的重要性2017-02-1612.SQL语句的优化建议2017-02-1413.通过分析等待类型解决系统产生问题2017-02-1314.磁盘问题定位与解决2017-02-0915.内存问题定位与解决2017-02-0816.写给数据库运维的兄弟2023-02-2717.为什么带NOLOCK的查询语句还会造成阻塞2023-02-2318.链接服务器查询导致的阻塞2023-02-2119.CXPACKET等待类型分析2023-02-2020.为什么sleeping的会话会造成阻塞(2)2023-02-1721.为什么sleeping的会话会造成阻塞2023-02-1422.为SQL Server配置连接加密2023-02-1423.SQL SERVER——高可用技术概述2023-02-0424.数据库服务器CPU不能全部利用原因分析2023-01-1525.管理工具造成的阻塞2023-01-0926.自动增长配置不合理导致的性能抖动2023-01-0527.Profiler导致的严重性能问题2023-01-0228.CPU持续100%分析并解决分析2022-12-2629.tempdb大量闩锁等待问题分析2022-11-2930.探究SQL SERVER 更改跟踪2022-11-1531.tempdb数据文件暴增分析2022-11-1032.SQL SERVER 2016 AlwaysOn 无域集群+负载均衡搭建与简测2024-12-1633.Moebius兼容AlwaysOn2024-12-1634.Moebius for SQL Server2024-12-1635.SQL SERVER日常运维巡检系列——数据库备份2024-12-0936.SQL SERVER日常运维巡检系列——结构设计2024-11-2637.Moebius for SQL Server2024-11-2038.SQL SERVER日常运维巡检系列之-数据库2024-10-0939.【能力提升】SQL Server常见问题介绍及快速解决建议2024-06-1940.CDC作业历史记录无法删除问题2024-03-0441.为Oracle链接服务器使用分布式事务2023-11-2142.大事务导致数据库恢复时间长2023-10-2543.SQL Server关于AlwaysOn的理解-读写分离的误区(一)2023-09-1444.链接服务器导致SQL Server停止响应2023-09-0545.简单配置Sql专家云2023-07-3146.SQL专家云回溯某时间段内的阻塞2023-07-0447.SQL专家云快速解决阻塞2023-06-1948.SQL专家云汇总低效的SQL语句2023-06-0549.数据库优化案例—某市中心医院HIS系统2023-05-1950.细说数据库协作运维2023-05-0851.医院核心数据库一体化建设实践2023-04-2452.【能力提升】SQL Server常见问题介绍及快速解决建议2023-04-1753.Moebius数据库多活集群2023-04-1054.30分钟带你熟练性能优化的那点儿事儿（案例说明）2023-04-0355.SQL Server底层架构技术对比2023-03-2856.Moebius兼容AlwaysOn2023-03-2357.透过等待看数据库2023-03-2158.数据库自动收缩造成的阻塞2023-03-0959.CPU持续100%分析并解决2023-03-02

收起

背景

某医院信息科接到CIS系统磁盘空间不足告警，通过排查发现tempdb的日志文件暴增，已经涨到了130G左右，并且还在持续增长中。需要我们紧急排查原因。

现象

登陆到服务器里，确实看到了如上所说，D盘空间仅剩14.5G，并且tempdb的日志文件已经达到了130G

 

 登录到SQL专家云，通过趋势分析进行回溯，在1月22日上午8点40分之前，tempdb日志文件的总大小（蓝线）一直保持在500M，使用空间（黄线）也能被重用。从这个时间点之后，总空间和使用空间一直增长。

 

分析

首先要了解一下tempdb日志文件重用的原理，因为tempdb的恢复模式是简单的，所以只要对tempdb做完了checkpoint后，这个时间点之前的空间就可以重复使用了。在SQL Server里面，所有的日志记录都有严格顺序，中间不可以有任何跳跃，如果某个时间点存在没有提交的事务，因为事务可能会回滚，这些日志记录都有可能需要被用来做回滚，因此SQL Server会标记从这个事务开始的所有日志记录（不管和这个事务有没有关系）为活动事务日志，导致日志文件不会被重用，只能是一直增长。据此推测在1月22日上午8点40分左右有一个或者多个会话有没有提交的事务，并且一直到现在为止都没有提交。进入SQL专家云的空闲会话页面，点击有未提交事务选项卡，开始查找这个时间段内的空闲会话，找到了ID为667的会话，空闲时间为16185分钟，语句最后请求结束时间正好对应上Tempdb开始增长的时间点。

 

点击进入完整信息，可以看到该会话在1月22日8点29分08秒建立的，在1月22日8点29分10秒开始了一个事务，在1月22日8点40分11秒后执行最后一条语句后不再执行语句，到目前为止该事务已经开了11天的时间。

 

 

解决

KILL掉这个会话，过几分钟后观察日志文件的使用空间已经下降。

 

但是日志文件的总大小是不变的，再执行收缩tempdb日志文件的命令即可释放掉磁盘的空间。

 

 

总结

这类问题的大多数原因是应用程序实现不严谨造成的，正常的流程下会提交事务，关闭数据库连接，但是如果中间某个步骤出错了，因为没有异常处理，在这个出错步骤后面的提交事务和关闭连接的代码都没有执行到，最终导致事务和连接的泄露。

 

 

 

所以根本的解决办法是修改程序，因为客观原因无法修改的，只能通过变通的方法来解决，例如在数据库中创建一个定期运行的作业，杀掉空闲时间长的会话。或者在SQL专家云中启用查杀会话的任务。

 

 

 

 

其它

很多客户也碰到过这样的现象，日志文件使用空间一直增长，很长时间内都不会下降，确认过肯定没有未提交的事务。这是因为tempdb的特殊性，日志文件使用率超过70%才会触发checkpoint，重用的快慢取决于tempdb日志文件的大小。例如日志文件的总大小为100GB， 使用空间只有增长到70GB才会checkpoint，然后使用空间才会下降。所以不要把日志文件设置的太大。