To find out how much tempdb disk space is occupied by each session that is connected and is idle in a SQL Server database, you can use the following query. This query leverages Dynamic Management Views (DMVs) like sys.dm_db_session_space_usage and sys.dm_exec_sessions to identify idle sessions and their tempdb usage.

Query to Find tempdb Disk Space Usage by Idle Sessions: 

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;

Explanation of the Query:

  1. sys.dm_db_session_space_usage:

    • Tracks tempdb space usage for each session.
    • Columns like user_objects_alloc_page_count and internal_objects_alloc_page_count show the number of 8 KB pages allocated for user objects (e.g., temporary tables) and internal objects (e.g., worktables).

  2. sys.dm_exec_sessions:

    • Provides session-level information, such as session_id, login_name, host_name, program_name, and status.
    • The status column is used to filter for idle sessions (status = 'sleeping').

  3. Calculations:

    • Multiply page counts by 8 / 1024.0 to convert pages to megabytes (MB).
    • Calculate the net space usage by subtracting deallocated pages from allocated pages.

  4. Filters:

    • es.status = 'sleeping': Filters for sessions that are idle (not actively running queries).
    • su.user_objects_alloc_page_count > 0 OR su.internal_objects_alloc_page_count > 0: Ensures only sessions using tempdb are included.

  5. Output:

    • 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.

Example Output:

session_id login_name host_name program_name status user_objects_alloc_mb user_objects_dealloc_mb internal_objects_alloc_mb internal_objects_dealloc_mb user_objects_net_mb internal_objects_net_mb
52 sa SQLHost01 SQLServerManagement sleeping 50.25 10.00 20.50 5.00 40.25 15.50
67 app_user AppServer01 MyApp.exe sleeping 30.00 30.00 0.00 0.00 0.00 0.00

Key Notes:

  • Idle sessions (status = 'sleeping') may still hold tempdb resources if they have not explicitly dropped temporary objects or if their scope has not ended.
  • If you notice high tempdb usage by idle sessions, investigate whether temporary objects are being properly cleaned up or if long-running transactions are holding resources.

This query is a great way to monitor and troubleshoot tempdb usage, especially in environments with high concurrency or large temporary object usage.