SQL Server Tempdb 200GB磁盘频繁已满问题排查咨询（无系统表权限）

Let’s walk through actionable steps you can take to diagnose your Tempdb space issue, even without direct access to system tables. The fact that this started right after an unexpected server reboot three months ago is a critical clue—we’ll use that to anchor our investigation.

• Collaborate with your DBA to capture real-time usage data
  Since you can’t query system tables directly, ask your DBA to run and share results from these tools, especially when Tempdb is filling up:

  • DBCC SQLPERF(LOGSPACE): This checks log space usage across all databases, including Tempdb. It’ll help you confirm if the bloat is in the data files or the log file.
  • Session/space DMVs: Have them query sys.dm_db_task_space_usage and sys.dm_db_session_space_usage to identify which active sessions are consuming the most Tempdb space. Even aggregated data (like the top 10 space-hogging sessions) will point you in the right direction.
  • sp_who2: A quick way to spot long-running sessions that might be holding onto temp tables, table variables, or version store data.

• Pinpoint problematic queries tied to daily bloat times
  Ask your DBA to set up either a server-side trace or Extended Events to capture queries that run around the two daily times when Tempdb fills up. Focus on queries that:

  • Perform large sorts, hashes, or use temp tables/table variables extensively.
  • Started behaving differently after the server reboot (e.g., slower execution, increased Tempdb usage).
    Post-reboot, query plans can change if statistics were reset or configuration settings shifted—this is a common culprit.

• Check for version store buildup
  Tempdb’s version store can balloon if long-running transactions prevent cleanup. Ask your DBA to:

  • Verify if READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION is enabled for any databases on the server—these features rely heavily on the version store.
  • Run DBCC OPENTRAN to check for open, long-running transactions that might be blocking version store cleanup.

• Review post-reboot configuration changes
  Unexpected reboots can reset or alter server-level settings. Have your DBA check:

  • Tempdb file setup: Number of data files (best practice is 1 per CPU core up to 8), auto-growth settings (avoid small fixed-size growths that cause fragmentation), and if files were set back to default sizes post-reboot.
  • Server memory settings: If max server memory was reduced after the reboot, more query operations might spill to Tempdb instead of using in-memory resources.
  • Auto-update statistics: If statistics weren’t refreshed after the reboot, inefficient query plans could lead to excessive Tempdb usage.

• Rule out scheduled jobs or external processes
  Check if there are daily scheduled jobs (ETL, reports, batch processes) that run exactly when Tempdb fills up. Ask your DBA to review SQL Server Agent job history for jobs that coincide with the bloat. Also, verify if third-party tools (backup, monitoring) are using Tempdb unexpectedly.

• Avoid relying solely on shrink operations
  DBCC SHRINKDATABASE or DBCC SHRINKFILE are temporary fixes that cause severe fragmentation, which can make Tempdb fill up faster over time. Focus on fixing the root cause instead of just shrinking repeatedly.

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