How to fix Error 1104 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 1104 – Tempdb Out of Space During Spilling

Why This Error Happens

This error occurs when a query operation—such as a hash join, sort, or large aggregation—spills intermediate results into tempdb, but tempdb’s data files have exhausted their available disk space or configured maximum size. This is typically caused by poorly optimized queries processing excessive row counts, missing or outdated statistics causing bad execution plans, or tempdb files that are undersized/not configured to autogrow properly. It can also happen if long-running transactions or orphaned sessions are holding tempdb space that never gets released.

Step-by-Step Resolution Guide

Step 1: Check Current Tempdb Space Usage

  • Open SQL Server Management Studio (SSMS) and run: EXEC sp_spaceused against tempdb, or query sys.dm_db_file_space_usage.
  • Check the physical disk location (commonly D:\SQLData\tempdb\ or E:\TempDB\) in Windows Explorer or via Get-PSDrive in PowerShell to confirm actual free disk space.
  • Identify if tempdb files have hit a configured MAXSIZE limit using sys.master_files.

Step 2: Identify and Terminate the Offending Query

  • Run sys.dm_db_task_space_usage joined with sys.dm_exec_requests to find sessions consuming the most tempdb space.
  • Use EXEC sp_who2 or Activity Monitor in SSMS to locate long-running or spilling queries.
  • Kill the problematic session if safe to do so: KILL <session_id>.

Step 3: Expand or Reconfigure Tempdb Files

  • Navigate to SSMS > Databases > System Databases > tempdb > Properties > Files.
  • Increase the initial file size and set autogrowth in fixed MB increments (not percentage) to avoid fragmentation—recommended: 512MB or 1GB increments.
  • Alternatively, run:
    ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 8192MB, FILEGROWTH = 1024MB);
  • Add additional tempdb data files (one per CPU core, up to 8) stored across separate physical disks if I/O contention is also a factor.

Step 4: Optimize the Query Causing Excessive Spilling

  • Open the execution plan in SSMS and look for warnings labeled “Sort” or “Hash Match” with high spill warnings.
  • Rewrite the query to filter rows earlier (push WHERE clauses before joins) and avoid SELECT *.
  • Update statistics on involved tables: UPDATE STATISTICS <table_name> WITH FULLSCAN;
  • Consider adding appropriate indexes to reduce the row count being processed in memory.

Step 5: Free Up Disk Space on the Tempdb Drive

  • Delete unnecessary files, old backups, or logs from the volume hosting tempdb.
  • If space is chronically limited, move tempdb to a dedicated drive with more capacity using:
    ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'F:\TempDB\tempdb.mdf');
  • Restart the SQL Server service (via services.msc or Restart-Service MSSQLSERVER) for the file relocation to take effect.

Alternative or Verification Steps

  • Re-run the original query and monitor sys.dm_db_file_space_usage in real time to confirm tempdb no longer approaches capacity.
  • If the issue persists, enable Trace Flag 1117 and 1118 (via SQL Server Configuration Manager startup parameters) to ensure uniform tempdb file growth and reduce allocation contention across multiple tempdb files.