Wednesday, May 04, 2011

Diagnosing tempdb Disk Space Problems

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