sys.dm_db_file_space_usage - Usage & Examples

sys.dm_db_file_space_usage exposes internal space allocation counters maintained by SQL Server's Page Free Space (PFS) pages and Global Allocation Map (GAM) pages for the current database context. The DMV reads directly from the database's allocation structures without requiring locks, making it lightweight for monitoring.

The allocated_extent_page_count reflects pages reserved through the mixed extent allocation process, while unallocated_extent_page_count shows available space in already-allocated extents. Version store reserved pages track row versioning overhead from READ_COMMITTED_SNAPSHOT, triggers, and online index operations.

SQL Server 2016 introduced significant tempdb improvements including multiple data files by default and trace flag 1117/1118 behavior changes. SQL Server 2019 added memory-optimized tempdb metadata, reducing allocation contention that this DMV monitors. SQL Server 2022 enhanced tempdb caching mechanisms, affecting how quickly space statistics update.

The internal_object_reserved_page_count captures space used by hash joins, sorts, and table spools. When this value grows rapidly, it indicates queries forcing operations to disk due to insufficient memory grants or poor execution plans.

-- Current tempdb space usage by allocation type
SELECT 
    allocated_extent_page_count * 8 / 1024 AS allocated_mb,
    unallocated_extent_page_count * 8 / 1024 AS unallocated_mb,
    version_store_reserved_page_count * 8 / 1024 AS version_store_mb,
    user_object_reserved_page_count * 8 / 1024 AS user_objects_mb,
    internal_object_reserved_page_count * 8 / 1024 AS internal_objects_mb,
    mixed_extent_page_count * 8 / 1024 AS mixed_extent_mb
FROM sys.dm_db_file_space_usage;

-- Tempdb space usage with file-level details
SELECT 
    f.name AS file_name,
    f.size * 8 / 1024 AS file_size_mb,
    su.allocated_extent_page_count * 8 / 1024 AS allocated_mb,
    (f.size - su.allocated_extent_page_count) * 8 / 1024 AS free_mb,
    CAST((su.allocated_extent_page_count * 100.0 / f.size) AS DECIMAL(5,2)) AS percent_used
FROM sys.dm_db_file_space_usage su
INNER JOIN sys.database_files f ON su.file_id = f.file_id
WHERE f.type = 0; -- Data files only

Note: sys.dm_db_file_space_usage is a point-in-time snapshot of current space usage. It does not store historical data. To track tempdb growth over time, capture results periodically into a monitoring table using a SQL Server Agent job.

-- Sessions contributing to tempdb usage
SELECT TOP 10
    s.session_id,
    s.login_name,
    s.program_name,
    tsu.user_objects_alloc_page_count * 8 / 1024 AS user_objects_mb,
    tsu.internal_objects_alloc_page_count * 8 / 1024 AS internal_objects_mb,
    r.command,
    r.percent_complete
FROM sys.dm_db_task_space_usage tsu
INNER JOIN sys.dm_exec_sessions s ON tsu.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE tsu.user_objects_alloc_page_count > 0 OR tsu.internal_objects_alloc_page_count > 0
ORDER BY (tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) DESC;

Identify and kill high tempdb consumers Terminates sessions consuming excessive tempdb space when automatic cleanup fails.

-- WARNING: Test in development first, kills active sessions
DECLARE @threshold_mb INT = 1000; -- Adjust threshold as needed
DECLARE @kill_cmd NVARCHAR(50);

DECLARE kill_cursor CURSOR FOR
SELECT 'KILL ' + CAST(session_id AS VARCHAR(10))
FROM sys.dm_db_task_space_usage
WHERE (user_objects_alloc_page_count + internal_objects_alloc_page_count) * 8 / 1024 > @threshold_mb
AND session_id > 50; -- Avoid system sessions

OPEN kill_cursor;
FETCH NEXT FROM kill_cursor INTO @kill_cmd;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Executing: ' + @kill_cmd;
    -- EXEC sp_executesql @kill_cmd; -- Uncomment to actually kill sessions
    FETCH NEXT FROM kill_cursor INTO @kill_cmd;
END

CLOSE kill_cursor;
DEALLOCATE kill_cursor;

Emergency tempdb file expansion Adds tempdb data files when space utilization exceeds safe thresholds.

-- Adds additional tempdb data file when current usage > 80%
USE master;
GO

DECLARE @current_usage_pct DECIMAL(5,2);
DECLARE @file_count INT;
DECLARE @new_file_path NVARCHAR(260);

SELECT @current_usage_pct = CAST((su.allocated_extent_page_count * 100.0 / f.size) AS DECIMAL(5,2))
FROM sys.dm_db_file_space_usage su
JOIN sys.database_files f ON f.file_id = su.file_id
WHERE f.type = 0;

SELECT @file_count = COUNT(*) FROM sys.database_files WHERE type = 0;

IF @current_usage_pct > 80 AND @file_count < 8 -- Limit to reasonable number
BEGIN
    SET @new_file_path = 'C:\tempdb_data\tempdb_data_' + CAST(@file_count + 1 AS VARCHAR(2)) + '.ndf';
    
    EXEC sp_executesql N'
    ALTER DATABASE tempdb 
    ADD FILE (
        NAME = ''tempdb_data_'' + @file_num,
        FILENAME = @filepath,
        SIZE = 1GB,
        FILEGROWTH = 256MB
    )', 
    N'@file_num VARCHAR(2), @filepath NVARCHAR(260)', 
    @file_num = CAST(@file_count + 1 AS VARCHAR(2)), 
    @filepath = @new_file_path;
    
    PRINT 'Added tempdb data file: ' + @new_file_path;
END
ELSE
    PRINT 'No tempdb expansion needed. Current usage: ' + CAST(@current_usage_pct AS VARCHAR(10)) + '%';

Clear version store when blocking tempdb growth Forces cleanup of version store pages consuming excessive tempdb space.

-- Clears long-running transactions blocking version store cleanup
-- WARNING: This will cause rollbacks and connection termination

SELECT 
    'KILL ' + CAST(session_id AS VARCHAR(10)) AS kill_command,
    transaction_begin_time,
    DATEDIFF(MINUTE, transaction_begin_time, GETDATE()) AS duration_minutes
FROM sys.dm_tran_active_transactions t
INNER JOIN sys.dm_tran_session_transactions st ON t.transaction_id = st.transaction_id
WHERE DATEDIFF(MINUTE, transaction_begin_time, GETDATE()) > 30 -- Transactions over 30 minutes
AND session_id > 50 -- Exclude system sessions
ORDER BY transaction_begin_time;

-- Uncomment to execute kills after review
-- Execute the KILL commands from above query output manually

Configure tempdb with multiple data files equal to CPU cores up to 8 files, each sized identically to prevent allocation contention. Enable instant file initialization on the SQL Server service account to eliminate initialization delays during growth events.

Set tempdb initial size to handle peak workloads without auto-growth. Monitor sys.dm_db_file_space_usage.allocated_extent_page_count trends and alert when exceeding 70% of total file space. Size tempdb files in 1GB increments with 256MB growth settings.

Implement query store or extended events to capture queries with high tempdb usage patterns. Focus on queries with large sorts, hash operations, and table variable usage exceeding 100MB. Create filtered indexes to reduce sorting requirements and optimize JOIN operations to minimize hash spills.

Configure READ_COMMITTED_SNAPSHOT isolation carefully in OLTP environments as version store overhead accumulates in tempdb. Monitor sys.dm_tran_version_store_space_usage alongside file space usage to detect version store bloat early.

Schedule regular maintenance windows to restart SQL Server instances experiencing persistent tempdb fragmentation, as tempdb cannot be shrunk below its initial size configuration during normal operations.