Home / Databases / SQL Server

DatabasesTroubleshootingAdvanced

SQL Server tempdb full or slow: find the consumer and fix contention

Identify which sessions and objects are filling tempdb, recognise PAGELATCH allocation contention, and configure tempdb files correctly.

Deepak Bhardwaj2 min read

Applies toSQL Server 2016–2022, SQL Server on Azure VMs and AWS EC2/RDS

Two different problems

  • tempdb full: error 1105 or 9002 for tempdb, queries failing with "Could not allocate space".
  • tempdb contention: tempdb isn't full, but many sessions wait on PAGELATCH_UP / PAGELATCH_EX on tempdb pages.

tempdb full: what uses the space?

-- By category
SELECT SUM(user_object_reserved_page_count)*8/1024     AS user_objects_mb,
       SUM(internal_object_reserved_page_count)*8/1024 AS internal_objects_mb,   -- sorts, hashes, spools
       SUM(version_store_reserved_page_count)*8/1024   AS version_store_mb,
       SUM(unallocated_extent_page_count)*8/1024       AS free_mb
FROM tempdb.sys.dm_db_file_space_usage;

-- By session
SELECT su.session_id, s.login_name, s.program_name,
       (su.user_objects_alloc_page_count + su.internal_objects_alloc_page_count)*8/1024 AS alloc_mb,
       t.text
FROM sys.dm_db_session_space_usage su
JOIN sys.dm_exec_sessions s ON su.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r ON su.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
ORDER BY alloc_mb DESC;
Big category Usual cause Fix
Internal objects Big sorts and hash joins spilling (missing indexes, bad estimates) Tune the query; update statistics
User objects Large temp tables or table variables Batch the work; index temp tables
Version store A long-running transaction with RCSI/snapshot isolation or an AG readable secondary Find the oldest transaction (sys.dm_tran_active_snapshot_database_transactions)

Contention

SELECT session_id, wait_type, wait_duration_ms, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH%' AND resource_description LIKE '2:%';

resource_description like 2:1:1 (PFS), 2:1:2 (GAM) or 2:1:3 (SGAM) on any file means allocation contention.

Fixes:

  1. Multiple equally sized data files: one per logical core up to 8; add 4 at a time beyond that if contention remains. Same size and same growth, so the proportional fill stays even.
  2. SQL Server 2019+: memory-optimized tempdb metadata removes system table contention:
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;   -- needs a restart
  1. Reduce temp table churn in hot procedures (cache temp tables by avoiding DDL after creation).

Configure tempdb properly

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 8GB, FILEGROWTH = 1GB);
ALTER DATABASE tempdb ADD FILE (NAME = temp2, FILENAME = 'T:\tempdb\temp2.ndf', SIZE = 8GB, FILEGROWTH = 1GB);

Put tempdb on fast local storage (on Azure VMs, the local temporary SSD D: drive is a common choice, with a startup script that recreates the folder).

Note

Restarting SQL Server empties tempdb and fixes "full" immediately, but the same query will fill it again. Find the consumer first.

Related fixes