SQL Server tempdb Deep Dive: Monitoring Usage and Reclaiming Space with Shrink Operations

Query 1: Identify tempdb Usage by Sessions That Could Be Released by Disconnecting

This query identifies sessions that are holding tempdb resources and could release them if disconnected. It focuses on sessions that are idle (sleeping) and still have allocated tempdb space.

SELECT 
    es.session_id,
    es.login_name,
    es.host_name,
    es.program_name,
    es.status,
    su.user_objects_alloc_page_count * 8 / 1024.0 AS user_objects_alloc_mb,
    su.user_objects_dealloc_page_count * 8 / 1024.0 AS user_objects_dealloc_mb,
    su.internal_objects_alloc_page_count * 8 / 1024.0 AS internal_objects_alloc_mb,
    su.internal_objects_dealloc_page_count * 8 / 1024.0 AS internal_objects_dealloc_mb,
    (su.user_objects_alloc_page_count - su.user_objects_dealloc_page_count) * 8 / 1024.0 AS user_objects_net_mb,
    (su.internal_objects_alloc_page_count - su.internal_objects_dealloc_page_count) * 8 / 1024.0 AS internal_objects_net_mb
FROM 
    sys.dm_db_session_space_usage su
JOIN 
    sys.dm_exec_sessions es
    ON su.session_id = es.session_id
WHERE 
    es.status = 'sleeping' -- Filter for idle sessions
    AND (su.user_objects_alloc_page_count > 0 OR su.internal_objects_alloc_page_count > 0) -- Filter for sessions using tempdb
ORDER BY 
    user_objects_net_mb DESC, internal_objects_net_mb DESC;

Key Outputs:

• user_objects_net_mb: Net space used by user objects (e.g., temporary tables) in MB.
• internal_objects_net_mb: Net space used by internal objects (e.g., worktables) in MB.
• session_id: The ID of the session holding the resources.
• status: Indicates if the session is idle (sleeping).

Action:

If these sessions are no longer needed, you can disconnect them using the following command:

KILL <session_id>;

---

Query 2: Estimate tempdb Disk Space That Can Be Reclaimed by Running SHRINKFILE

This query estimates the amount of tempdb space that can be reclaimed by running SHRINKFILE. It uses the sys.dm_db_file_space_usage DMV to identify unused space in tempdb.

SELECT 
    name AS file_name,
    size * 8 / 1024.0 AS current_size_mb,
    FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024.0 AS used_space_mb,
    (size - FILEPROPERTY(name, 'SpaceUsed')) * 8 / 1024.0 AS free_space_mb
FROM 
    sys.master_files
WHERE 
    database_id = DB_ID('tempdb')
    AND type = 0; -- 0 = Data file, 1 = Log file

Key Outputs:

• current_size_mb: Current size of the tempdb data file in MB.
• used_space_mb: Space actively used in the tempdb data file in MB.
• free_space_mb: Free space that can potentially be reclaimed by shrinking the tempdb data file.

Action:

If there is significant free space, you can reclaim it by running the following command:

USE tempdb;
DBCC SHRINKFILE ('tempdev', target_size_in_mb); -- Replace 'tempdev' with the logical name of your tempdb data file

---

Important Notes:

1. Disconnecting Sessions:

   • Disconnecting sessions should be done cautiously, as it may disrupt active processes or users.
   • Ensure the sessions are truly idle and no longer needed before killing them.

2. Shrinking tempdb:

   • Shrinking tempdb is generally not recommended unless absolutely necessary, as it can lead to fragmentation and performance overhead.
   • If tempdb grows frequently, consider increasing its initial size to avoid frequent auto-growth events.

3. Monitoring:

   • Regularly monitor tempdb usage using the provided queries to proactively manage resources and avoid contention.