Running out of disk space in TempDB can cause significant disruptions in the SQL Server production environment and can prevent applications that are running from completing operations. You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space that is used in the TempDB files:
-- Determining the Amount of Free Space in TempDB SELECT SUM(unallocated_extent_page_count) AS [free pages], (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB] FROM sys.dm_db_file_space_usage; -- Determining the Amount Space Used by the Version Store SELECT SUM(version_store_reserved_page_count) AS [version store pages used], (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB] FROM sys.dm_db_file_space_usage; -- Determining the Amount of Space Used by Internal Objects SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used], (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB] FROM sys.dm_db_file_space_usage; -- Determining the Amount of Space Used by User Objects SELECT SUM(user_object_reserved_page_count) AS [user object pages used], (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB] FROM sys.dm_db_file_space_usage;
Additionally, to monitor the page allocation or deallocation activity in TempDB at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. These views can be used to identify large queries, temporary tables, or table variables that are using lots of TempDB disk space.
-- Obtaining the space consumed by internal objects in all currently running tasks in each session SELECT session_id, SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count, SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count FROM sys.dm_db_task_space_usage GROUP BY session_id; -- Obtaining the space consumed by internal objects in the current session for both running and completed tasks SELECT R2.session_id, R1.internal_objects_alloc_page_count + SUM(R2.internal_objects_alloc_page_count) AS session_internal_objects_alloc_page_count, R1.internal_objects_dealloc_page_count + SUM(R2.internal_objects_dealloc_page_count) AS session_internal_objects_dealloc_page_count FROM sys.dm_db_session_space_usage AS R1 INNER JOIN sys.dm_db_task_space_usage AS R2 ON R1.session_id = R2.session_id GROUP BY R2.session_id, R1.internal_objects_alloc_page_count, R1.internal_objects_dealloc_page_count;
Data is fascinating.
I make SQL Server faster and more reliable. Database administration isn’t about passing a certified exam, or about pointing and clicking your way through a crisis. Database administration is about applying the right solution at the right time, avoiding risk, and making robust choices that get you home each night in time for dinner with your family .
My areas of specialty are:
• SQL Server 2019-2008 performance tuning.
• Designing high availability and disaster recovery solutions.
• Securing SQL Server
• SSIS , SSAS
• Designing Data warehouse