How to Fix a Full TempDB in MS SQL Server

TempDB is a critical system database for temporary operations. A full TempDB can cause performance degradation and failed transactions. Follow these steps to resolve and prevent issues.

1. Diagnose TempDB Space Usage

Check overall usage:

USE tempdb;
EXEC sp_spaceused;

Detailed space breakdown:

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)],
    SUM(version_store_reserved_page_count) * 8 / 1024 AS [Version Store (MB)],
    SUM(unallocated_extent_page_count) * 8 / 1024 AS [Free Space (MB)]
FROM sys.dm_db_file_space_usage;

2. Address Active Sessions

Identify sessions consuming TempDB:

SELECT 
    session_id, 
    login_name,
    host_name,
    SUM(internal_objects_alloc_page_count) * 8 / 1024 AS [Internal Objects (MB)],
    SUM(user_objects_alloc_page_count) * 8 / 1024 AS [User Objects (MB)]
FROM sys.dm_db_session_space_usage
JOIN sys.dm_exec_sessions ON session_id = session_id
GROUP BY session_id, login_name, host_name
ORDER BY [Internal Objects (MB)] + [User Objects (MB)] DESC;

Terminate problematic sessions:

KILL <session_id>; -- Replace with actual session ID

3. Manage TempDB Size

Shrink individual TempDB files:

DBCC SHRINKFILE (tempdev, 10240); -- Shrink to 10GB
DBCC SHRINKFILE (templog, 1024); -- Shrink log to 1GB

Check file sizes and growth settings:

SELECT 
    name,
    size * 8 / 1024 AS [Size (MB)],
    growth,
    is_percent_growth
FROM tempdb.sys.database_files;

4. Optimize TempDB Configuration

Increase file sizes and add multiple files:

Adjust existing files

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 8GB, FILEGROWTH = 1GB);
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, SIZE = 2GB, FILEGROWTH = 512MB);

Add new data files (1 per CPU core, up to 8)

ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, FILENAME = 'D:\MSSQL\tempdb2.ndf', SIZE = 8GB, FILEGROWTH = 1GB);
ALTER DATABASE tempdb ADD FILE (NAME = tempdev3, FILENAME = 'D:\MSSQL\tempdb3.ndf', SIZE = 8GB, FILEGROWTH = 1GB);

5. Prevent Future Issues

Implement proactive monitoring:

Create alert for TempDB space

EXEC msdb.dbo.sp_add_alert 
    @name = N'TempDB Space Warning',
    @message_id = 0,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 300,
    @notification_message = N'TempDB space is critically low',
    @performance_condition = N'Databases|Percent Log Used|tempdb|>|90';

Check version store retention:

SELECT 
    transaction_id,
    elapsed_time_seconds,
    session_id 
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;

6. Post-Resolution Checklist

• Restart SQL Server if space cannot be reclaimed (TempDB resets on restart)
• Place TempDB files on fast storage separate from user databases
• Review queries for excessive temp table usage or sorting operations
• Ensure all TempDB data files are equal size for proportional fill

Note : No Full Backup or differential backup will be taken for Temp DB.(Because all operation are before commit of data..i.e it will be commit or rollback. )