TempDB Filling Up? Here’s What to Check: A DBA's Survival Guide

Introduction

TempDB is SQL Server's "scratch pad" database—used for temporary objects, sorting operations, version stores, and more. When it fills up unexpectedly, it can bring your entire SQL Server instance to its knees. In this post, I'll walk you through the essential checks to diagnose and resolve TempDB space issues.

Why TempDB Fills Up (And Why It Matters)

TempDB issues manifest in several ways:

• Queries failing with "TempDB is full" errors

• Sudden performance degradation across the entire instance

• Applications timing out or behaving unpredictably

When TempDB runs out of space, it affects all databases on the instance, making this a critical issue to address quickly.

Immediate Checks When TempDB Is Full

1. Identify What's Consuming Space

-- Check space usage by object type
SELECT
    session_id,
    task_alloc,
    task_dealloc,
    internal_objects_alloc_page_count,
    internal_objects_dealloc_page_count,
    user_objects_alloc_page_count,
    user_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage;

-- Check version store usage (for snapshot isolation)
SELECT
    database_id,
    reserved_page_count,
    reserved_space_kb/1024 AS reserved_space_mb
FROM sys.dm_tran_version_store_space_usage;

2. Find Problematic Queries

-- Identify queries using the most TempDB resources
SELECT
    t.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    t.internal_objects_alloc_page_count,
    t.internal_objects_dealloc_page_count,
    t.user_objects_alloc_page_count,
    t.user_objects_dealloc_page_count,
    q.text AS query_text
FROM sys.dm_db_task_space_usage t
JOIN sys.dm_exec_sessions s ON t.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(
    (SELECT sql_handle FROM sys.dm_exec_requests
     WHERE session_id = t.session_id)
) q
ORDER BY (t.internal_objects_alloc_page_count +
          t.user_objects_alloc_page_count) DESC;

Common Causes of TempDB Growth

1. Version Store Growth: From snapshot isolation or triggers

2. Large Sort/Hash Operations: Memory grants spilling to disk

3. Explicit Temp Tables: Especially unoptimized ones

4. DBCC CHECKDB: During maintenance operations

5. Cursor Operations: Certain types of cursors use TempDB heavily

6. Query Spools: The query optimizer's internal operations

Long-Term Solutions

Configuration Best Practices

• Multiple Data Files: Create 1 TempDB data file per CPU core (up to 8)

• Proper Sizing: Pre-size TempDB files to avoid autogrowth events

• Separate Drive: Place TempDB on its own fast storage

Monitoring Setup

-- Create a baseline table
CREATE TABLE TempDB_Monitoring (
    collection_time DATETIME,
    version_store_mb FLOAT,
    internal_object_mb FLOAT,
    user_object_mb FLOAT,
    total_used_mb FLOAT
);

-- Collect regular snapshots
INSERT INTO TempDB_Monitoring
SELECT
    GETDATE(),
    SUM(version_store_reserved_page_count)*8/1024.0,
    SUM(internal_object_reserved_page_count)*8/1024.0,
    SUM(user_object_reserved_page_count)*8/1024.0,
    SUM(version_store_reserved_page_count +
        internal_object_reserved_page_count +
        user_object_reserved_page_count)*8/1024.0
FROM sys.dm_db_file_space_usage;

Query Optimization

Look for:

• Excessive sorting (large ORDER BY operations)

• Hash joins spilling to disk

• Complex common table expressions (CTEs)

• Overuse of temporary tables instead of table variables

Emergency Actions

If TempDB is completely full and you need immediate relief:

1. Restart SQL Server: Clears all temporary objects (not ideal for production)

2. Kill Problem Sessions: Use the queries above to identify and terminate offenders

3. Add TempDB Files: Temporarily add files to a different drive

Conclusion

TempDB issues can be stressful, but with the right approach you can quickly identify the root cause. Regular monitoring and proper configuration will help prevent most problems before they occur. Remember—an ounce of TempDB prevention is worth a pound of emergency troubleshooting!

Have you encountered particularly nasty TempDB issues? Share your war stories and solutions in the comments below!