SQL Server中Templog日志文件每日自动增长问题解决方案咨询

首先明确：严禁开启auto-shrink（自动收缩）功能 #

自动收缩是SQL Server所有配置里公认的生产环境反实践：

• 它会在后台不定期触发文件收缩，收缩后业务写入又会触发文件重新增长，反复的收缩-增长循环会产生大量文件碎片，直接拖慢所有涉及tempdb的查询性能
• 自动收缩触发时机不可控，收缩过程中会占用大量磁盘IO、持有文件锁，极易引发业务查询阻塞、死锁，甚至导致服务响应超时
• 对tempdb而言，自动收缩完全无法解决日志持续增长的根因，反而会放大性能问题，微软官方明确不建议任何生产环境开启该配置。

第一步：定位日志暴涨的根本原因 #

tempdb日志持续增长无法自动释放，本质是日志截断被阻塞，优先排查三类问题：

• 存在长时间未提交的活动事务
  tempdb默认使用简单恢复模式，正常情况下每次检查点触发时就会自动截断无用日志，但只要存在跨时间的活动事务，事务启动后生成的所有日志都不会被截断，哪怕事务本身没有写入操作，也会导致日志持续累积。
  可以用以下语句直接查询tempdb中运行时间最长的活动事务及对应来源查询：

  SELECT 
      d.transaction_id,
      t.transaction_begin_time,
      s.session_id,
      s.host_name,
      s.program_name,
      s.login_name,
      r.command,
      SUBSTRING(st.text, (r.statement_start_offset/2)+1,
      ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
      ELSE r.statement_end_offset END - r.statement_start_offset)/2)+1) AS running_sql
  FROM sys.dm_tran_database_transactions d
  JOIN sys.dm_tran_session_transactions st ON d.transaction_id = st.transaction_id
  JOIN sys.dm_exec_sessions s ON st.session_id = s.session_id
  LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
  LEFT JOIN sys.dm_tran_active_transactions t ON d.transaction_id = t.transaction_id
  OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st
  WHERE d.database_id = DB_ID('tempdb')
  ORDER BY t.transaction_begin_time ASC;
  

• TempDB空间被三类对象异常占用
  执行以下语句查看tempdb的空间分布，定位占用来源：

  SELECT 
      SUM(user_object_reserved_page_count)*8/1024 AS 用户临时对象占用_MB,
      SUM(internal_object_reserved_page_count)*8/1024 AS 内部运算对象占用_MB,
      SUM(version_store_reserved_page_count)*8/1024 AS 行版本存储占用_MB
  FROM tempdb.sys.dm_db_file_space_usage;
  

  • 用户临时对象占用高：检查是否有会话创建了超大临时表/表变量、用完未释放，或是全局临时表长期驻留
  • 内部运算对象占用高：检查是否有大量查询因为缺少索引，触发了大结果集排序、哈希连接、哈希聚合溢出到tempdb的情况
  • 行版本存储占用高：检查是否开启了读提交快照隔离级别、快照隔离级别，存在长时间运行的事务导致旧版本无法清理
• 配置错误
  执行以下语句确认tempdb恢复模式：

  SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'tempdb';
  

  如果返回结果不是SIMPLE（简单恢复模式），说明配置被篡改，需要立刻改回简单恢复模式——tempdb不支持日志备份，用完整/大容量日志模式会导致日志永远无法自动截断。

第二步：根治方案（无需每日手动收缩） #

• 预分配TempDB文件大小：观察1-2周的tempdb文件峰值占用（当前场景每日峰值是10-12GB），直接将tempdb日志文件、数据文件的初始大小设置为比峰值高20%左右（比如设为14GB），关闭按百分比自动增长，设置固定增长步长：日志文件每次增长设为512MB-1GB，数据文件每次增长设为1-2GB，避免频繁的文件零初始化开销。
• 清理问题负载：杀掉定位到的长时间空闲持有事务的会话，优化占用tempdb极高的查询：给常用排序、关联字段加索引避免查询溢出到tempdb，临时表用完及时显式删除，避免隐式事务长期不提交，游标用完及时关闭释放。
• 调整检查点频率：如果日常写入量极大，可以适当调整检查点触发间隔，保证日志能被及时截断，不需要做定时收缩操作。
• 仅在异常场景做一次性收缩：只有当偶发的异常操作（比如一次性导入超大量数据、bug导致的死循环写入）导致tempdb日志增长到远高于日常峰值的大小，且后续业务不会用到这么大空间时，再手动做一次收缩即可，不需要设置每日收缩任务。

内容的提问来源于stack exchange，提问作者Dharmishtha