Windows Server 2022 — SQL Server tempdb growth fills system volume — Fix & Prevention

📖 ~1 min read

Table of contents

1. Symptom & Impact
2. Environment & Reproduction
3. Root Cause Analysis
4. Quick Triage
5. Step-by-Step Diagnosis
6. Solution — Primary Fix
7. Solution — Alternative Approaches
8. Verification & Acceptance Criteria
9. Rollback Plan
10. Prevention & Hardening
11. Related Errors & Cross-Refs
12. References & Further Reading

Symptom & Impact

Rapid tempdb growth exhausts system disk and causes query failures, SQL stalls, and possible OS-level instability. Critical app transactions and report generation fail until free space is restored.

Environment & Reproduction

Usually triggered by large sorts/hash joins, temp table misuse, missing indexes, or runaway ETL jobs.

Get-PSDrive -PSProvider FileSystem
Invoke-Sqlcmd -Query "SELECT name,size*8/1024 AS sizeMB FROM sys.master_files WHERE database_id=2"
Get-WinEvent -LogName Application -MaxEvents 30 | Where-Object {$_.Message -match 'tempdb|insufficient disk'}

Root Cause Analysis

Poor query plans, unrestricted autogrowth, and tempdb placement on constrained volumes combine to produce sudden storage exhaustion under workload spikes.

Quick Triage

Protect the host first: reclaim disk safely, identify largest tempdb consumers, and halt nonessential jobs.

Get-ChildItem C:/ -Recurse -ErrorAction SilentlyContinue | Sort-Object Length -Descending | Select-Object -First 20 FullName,Length
Invoke-Sqlcmd -Query "SELECT TOP 20 session_id,user_objects_alloc_page_count,internal_objects_alloc_page_count FROM sys.dm_db_session_space_usage ORDER BY (user_objects_alloc_page_count+internal_objects_alloc_page_count) DESC"

Step-by-Step Diagnosis

Confirm file layout, autogrowth settings, and workload patterns generating heavy temporary allocations.

Invoke-Sqlcmd -Query "SELECT name,physical_name,size*8/1024 AS sizeMB,growth,is_percent_growth FROM sys.master_files WHERE database_id=2"
Invoke-Sqlcmd -Query "SELECT TOP 20 total_elapsed_time/1000 AS elapsed_ms,text FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) ORDER BY total_elapsed_time DESC"

Solution — Primary Fix

Move tempdb to dedicated volume, pre-size files evenly, and cap growth increments to avoid fragmentation and burst expansion.

Invoke-Sqlcmd -Query "ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, SIZE=4096MB, FILEGROWTH=256MB)"
Invoke-Sqlcmd -Query "ALTER DATABASE tempdb MODIFY FILE (NAME=templog, SIZE=1024MB, FILEGROWTH=256MB)"
Restart-Service MSSQLSERVER -Force

Solution — Alternative Approaches

If immediate relocation is blocked, throttle heavy ETL, add emergency disk, and tune worst-offending query plans/indexes.

Disable-ScheduledTask -TaskName '' -ErrorAction SilentlyContinue
Invoke-Sqlcmd -Query "DBCC FREEPROCCACHE"
Invoke-Sqlcmd -Query "EXEC sp_updatestats"

Verification & Acceptance Criteria

Successful resolution means stable free disk headroom, controlled tempdb growth, and no repeat storage-related SQL failures.

Get-PSDrive C
Invoke-Sqlcmd -Query "SELECT name,size*8/1024 AS sizeMB FROM sys.master_files WHERE database_id=2"
Get-WinEvent -LogName Application -MaxEvents 20 | Where-Object {$_.Message -match 'tempdb'}

Rollback Plan

If performance regresses after tempdb changes, revert file sizes/growth values and restart SQL in maintenance window.

Invoke-Sqlcmd -Query "ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, SIZE=1024MB, FILEGROWTH=10%)"
Restart-Service MSSQLSERVER

Prevention & Hardening

Use dedicated tempdb volume, fixed growth, query tuning guardrails, and proactive disk alerts with low-latency thresholds.

Get-Counter 'LogicalDisk(C:)% Free Space'
Register-ScheduledTask -TaskName 'TempdbCheck' -Action (New-ScheduledTaskAction -Execute 'powershell.exe' -Argument '-Command "Get-PSDrive C"') -Trigger (New-ScheduledTaskTrigger -Once -At 03:00 -RepetitionInterval (New-TimeSpan -Minutes 30))

Related Errors & Cross-Refs

Related to sort/hash spill warnings, insufficient disk errors, and memory grant pressure causing excessive temporary object generation.

View all Windows Server 2022 tutorials on the Tutorials Hub →

Browse all common problems & solutions on the Tutorials Hub.

References & Further Reading

Microsoft tempdb best practices, SQL Server storage tuning documentation, and operational playbooks for disk pressure response on Windows Server 2022.