ALL About TempDB

For TempDB

--Determining the amount of free space in tempdb

USE 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 of Space Used by User Objects

USE tempdb; 

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;

 

use tempdb 

SELECT GETDATE() AS runtime

    ,(SUM(user_object_reserved_page_count) * 8)/1024 AS usr_obj_mb

    ,SUM(internal_object_reserved_page_count) * 8 AS internal_obj_kb

    ,(SUM(version_store_reserved_page_count) * 8)/1024 AS version_store_mb

    ,(SUM(unallocated_extent_page_count) * 8)/1024 AS freespace_mb

    ,(SUM(mixed_extent_page_count) * 8)/1024 AS mixedextent_mb

FROM sys.dm_db_file_space_usage

 

use tempdb  

SELECT session_id,

    SUM(internal_objects_alloc_page_count) AS NumOfPagesAllocatedInTempDBforInternalTask,

    SUM(internal_objects_dealloc_page_count) AS NumOfPagesDellocatedInTempDBforInternalTask,

    SUM(user_objects_alloc_page_count) AS NumOfPagesAllocatedInTempDBforUserTask,

    SUM(user_objects_dealloc_page_count) AS NumOfPagesDellocatedInTempDBforUserTask

FROM sys.dm_db_task_space_usage

GROUP BY session_id

ORDER BY NumOfPagesAllocatedInTempDBforInternalTask DESC, NumOfPagesAllocatedInTempDBforUserTask DESC

 

select * from sys.dm_db_task_space_usage

select *,db_name(database_id) from sys.dm_db_session_space_usage

 

Alter TempDB File Size

use master

ALTER DATABASE tempdb

MODIFY FILE (Name=N'tempdev', size = 176080MB)

For further information, click on the following link.

https://clotat.blogspot.com/2020/05/all-about-tempdb.html