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 s…


This content originally appeared on DEV Community and was authored by Arvind Toorpu

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.


This content originally appeared on DEV Community and was authored by Arvind Toorpu


Print Share Comment Cite Upload Translate Updates
APA

Arvind Toorpu | Sciencx (2025-02-26T19:40:30+00:00) SQL Server tempdb Deep Dive: Monitoring Usage and Reclaiming Space with Shrink Operations. Retrieved from https://www.scien.cx/2025/02/26/sql-server-tempdb-deep-dive-monitoring-usage-and-reclaiming-space-with-shrink-operations/

MLA
" » SQL Server tempdb Deep Dive: Monitoring Usage and Reclaiming Space with Shrink Operations." Arvind Toorpu | Sciencx - Wednesday February 26, 2025, https://www.scien.cx/2025/02/26/sql-server-tempdb-deep-dive-monitoring-usage-and-reclaiming-space-with-shrink-operations/
HARVARD
Arvind Toorpu | Sciencx Wednesday February 26, 2025 » SQL Server tempdb Deep Dive: Monitoring Usage and Reclaiming Space with Shrink Operations., viewed ,<https://www.scien.cx/2025/02/26/sql-server-tempdb-deep-dive-monitoring-usage-and-reclaiming-space-with-shrink-operations/>
VANCOUVER
Arvind Toorpu | Sciencx - » SQL Server tempdb Deep Dive: Monitoring Usage and Reclaiming Space with Shrink Operations. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2025/02/26/sql-server-tempdb-deep-dive-monitoring-usage-and-reclaiming-space-with-shrink-operations/
CHICAGO
" » SQL Server tempdb Deep Dive: Monitoring Usage and Reclaiming Space with Shrink Operations." Arvind Toorpu | Sciencx - Accessed . https://www.scien.cx/2025/02/26/sql-server-tempdb-deep-dive-monitoring-usage-and-reclaiming-space-with-shrink-operations/
IEEE
" » SQL Server tempdb Deep Dive: Monitoring Usage and Reclaiming Space with Shrink Operations." Arvind Toorpu | Sciencx [Online]. Available: https://www.scien.cx/2025/02/26/sql-server-tempdb-deep-dive-monitoring-usage-and-reclaiming-space-with-shrink-operations/. [Accessed: ]
rf:citation
» SQL Server tempdb Deep Dive: Monitoring Usage and Reclaiming Space with Shrink Operations | Arvind Toorpu | Sciencx | https://www.scien.cx/2025/02/26/sql-server-tempdb-deep-dive-monitoring-usage-and-reclaiming-space-with-shrink-operations/ |

Please log in to upload a file.



There are no updates yet.
Click the Upload button above to add an update.

You must be logged in to translate posts. Please log in or register.

Related Posts