Thursday, 21 November 2013

Script to monitor tempdb usage in SQL Server



Use the below query to monitor the tempdb usage in SQL Server.

The following query returns the total number of free pages and total free space in megabytes (MB) available in all files 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