-- Returns space usage of each file in tempdb
select * from sys.dm_db_file_space_usage
--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 Longest Running Transaction
SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;
--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 Internal and user Objects in tempdb
SELECT SUM(internal_object_reserved_page_count) as Internal_Pages,
(SUM(internal_object_reserved_page_count)*1.0/128) as Internal_space_MB,
SUM(User_object_reserved_page_count) as User_Pages,
(SUM(internal_object_reserved_page_count)*1.0/128) as User_space_MB
FROM sys.dm_db_file_space_usage;
--Determining the Total Amount of Space (Free and Used)
SELECT SUM(size)*1.0/128 AS [size in MB]
FROM tempdb.sys.database_files
