生产环境无法删除额外tempdb文件，主文件未正常收缩

嘿，我来帮你搞定这个棘手的tempdb问题！咱们一步步来，先解决删不掉tempdev2的麻烦，再处理主文件收缩和后续优化。

一、先搞定无法删除tempdev2的报错 #

你遇到的Could not locate file 'tempdev2' for database 'tempdb'错误，大概率是系统目录里记录了这个文件，但物理文件要么没创建完整、路径不对，要么已经被意外删除了。咱们按以下步骤排查：

1. 确认文件的系统记录与物理路径
   先执行这条查询，获取tempdb所有文件的详细信息：

   SELECT name, file_id, physical_name, state_desc, size 
   FROM sys.master_files 
   WHERE database_id = DB_ID('tempdb');
   

   找到tempdev2对应的physical_name，去服务器上的这个路径检查：

   • 如果物理文件不存在：说明添加操作中途失败，系统只留了记录但没生成实际文件。
   • 如果物理文件存在：那可能是文件权限问题，或者有会话在占用它。

2. 处理物理文件不存在的情况
   如果物理文件没了，咱们可以先手动在对应的路径创建一个空的同名文件（注意文件后缀要和其他tempdb文件一致，比如.ndf），然后给SQL Server服务账号赋予这个文件的读写权限。
   之后再执行删除语句：

   ALTER DATABASE tempdb REMOVE FILE tempdev2;
   

3. 处理物理文件存在但无法删除的情况
   先检查有没有会话在使用这个文件：

   SELECT s.session_id, s.login_name, t.text
   FROM sys.dm_exec_sessions s
   JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
   CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
   WHERE r.database_id = DB_ID('tempdb') 
     AND r.file_id = (SELECT file_id FROM sys.master_files WHERE name = 'tempdev2');
   

   如果查到活跃会话，先终止这些会话（注意：生产环境要谨慎，确认不会影响业务）：

   KILL [session_id];
   

   之后再执行删除语句。如果还是不行，尝试重启SQL Server服务——因为tempdb是临时数据库，重启后会重建所有配置的文件，之后再删除tempdev2会更顺利。

二、收缩主tempdb文件到30GB #

之前收缩失败是因为磁盘空间不足，咱们得先腾空间，再操作：

1. 先减少tempdb的使用量

   • 终止所有不必要的大查询、报表任务，这些任务可能在占用大量tempdb空间。
   • 清理临时对象：执行DBCC FREEPROCCACHE;（清理计划缓存）和DBCC DROPCLEANBUFFERS;（清理数据缓存），但生产环境要在业务低峰期操作，避免影响性能。

2. 执行收缩命令
   确保磁盘有足够的临时空间（至少要能容纳收缩过程中产生的临时数据），然后执行：

   DBCC SHRINKFILE (tempdev, 30720); -- 30720MB = 30GB
   

   注意：收缩操作会产生碎片，如果之后tempdb又要膨胀，性能会受影响，所以收缩后最好调整自动增长设置，避免单文件再次膨胀。

三、优化tempdb的长期配置 #

为了避免以后再出现单文件膨胀的问题，建议按最佳实践配置：

• 添加多个大小一致的tempdb数据文件（数量建议等于CPU核心数，最多8个），每个文件初始大小设为30GB，和主文件一致。
• 把所有tempdb数据文件的自动增长设置为固定大小（比如每次增长2GB），不要用百分比，避免文件大小不均。
• 确保tempdb所在磁盘有足够的冗余空间，避免再次出现磁盘不足的情况。

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