How to fix tempdb disk space issue in SQL Server?

I always get tempdb disk space issue in one of my prod server. 

Here’s a step-by-step guide:

---

1. Identify the Issue

a. Check tempdb size and usage:

USE tempdb;
GO
EXEC sp_spaceused;

b. Check file size and free space:

SELECT
    name AS [File Name],
    size / 128.0 AS [Total Size in MB],
    size / 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0 AS [Free Space in MB]
FROM tempdb.sys.database_files;

c. See active sessions using tempdb:

SELECT
    s.session_id,
    r.status,
    r.command,
    r.cpu_time,
    r.total_elapsed_time,
    t.text
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.database_id = 2;

d. Tempdb contention or allocation info:

DBCC SQLPERF('sys.dm_os_wait_stats'); -- Look for PAGELATCH_xx

---

2. Workaround (Temporary Fix)

a. Shrink tempdb (Not ideal, use only when necessary):

USE tempdb;
GO
DBCC SHRINKFILE(tempdev, TARGET_SIZE_MB); -- Example: DBCC SHRINKFILE(tempdev, 1000);
DBCC SHRINKFILE(templog, TARGET_SIZE_MB);

Replace TARGET_SIZE_MB with your desired size. Find logical file names via sys.database_files.

Note: This only works when there's unallocated space. It doesn't shrink active objects.

b. Restart SQL Server (emergency fix)

• This clears tempdb completely, but causes downtime.
• Not recommended unless no other choice.

---

3. Permanent Fix

a. Add multiple tempdb data files:

• Reduces contention and spreads workload.
• Recommended: 1 data file per logical CPU (up to 8).

-- Add new tempdb data files
ALTER DATABASE tempdb ADD FILE (
    NAME = tempdev2,
    FILENAME = 'D:\SQLData\tempdev2.ndf',
    SIZE = 256MB,
    FILEGROWTH = 64MB
);
-- Repeat as needed

b. Set proper initial size and autogrowth:

Avoid small autogrowth settings.

ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, SIZE = 1GB, FILEGROWTH = 256MB);

c. Monitor queries using large tempdb space:

• Identify bad queries (e.g., sorting, temp tables, hash joins).
• Tune them (use indexes, reduce temp objects, rewrite logic).

SELECT 
    t.text,
    r.tempdb_allocations,
    r.tempdb_current
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.database_id = 2;

d. Move tempdb to a fast disk:

• Place it on SSD or a separate drive for performance.

-- Change tempdb file location (requires restart)
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLTempDB\tempdev.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLTempDB\templog.ldf');

After modifying, restart SQL Server.

---

4. Preventive Maintenance

• Regularly monitor tempdb usage with custom scripts or tools.
• Set up alerts for disk space usage.
• Avoid large temp tables or excessive cursor operations.