SQL Server reported that our tempdb is out of space. So I had a quick google search and found this on technet


Determining the Amount of Free Space in tempdb

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;

This is the result of the query:

free pages           free space in MB
-------------------- ---------------------------------------
1272                 9.937500

The strange thing is that when i go and check it in the ui of the ssms the size and the free space is perfectly fine

enter image description here

So here are my questions:

  1. Why are they reporting two different sizes. Is this due that the script actually counts the page file size rather than "hole size"?
  2. Why is the script dividing the sum by 128?

Any answer is greatly appreciated.

  • 1 Page is 8 Kb. 1Mb is 1024 Kb. Since sys.dm_db_file_space_usage gives page count, to get MB you have to multiply by 8 and then divide by 1024. This can be simplified to divide by 128, since 1024/8 = 128. – Remus Rusanu Jun 23 '16 at 13:51
  • Neat trick. Thanks for that. But do you have any idea why the results differ? – Hans Vader Jun 23 '16 at 13:56
  • 1
    The results differ because SSMS sums all of the files regardless of type whereas your query only looks at data files. This means you have a very large tempdb log file that's empty and data files that are full. – Sean Gallardy - Retired User Jun 23 '16 at 14:42

You may want to consider using the query that will show you free space in the log and free space in the data file. If you add them up you'll get the similar value as the SSMS is showing.

This is an excellent query which does just that:

DECLARE @log_used DECIMAL(19,7);
SELECT @log_used = u FROM #x WHERE n = DB_NAME();

DECLARE @data_used DECIMAL(19,7);
SELECT @data_used = SUM(a.total_pages)*8/1024.0
FROM sys.partitions AS p 
INNER JOIN sys.allocation_units AS a 
ON p.[partition_id] = a.container_id;

;WITH x(t,s) AS
  SELECT [type] = CASE 
    WHEN [type] IN (0,2,4) THEN 'data' ELSE 'log' END, 
    size*8/1024.0 FROM sys.database_files AS f
  file_type = t, 
  size = s,
  available = s-CASE t WHEN 'data' THEN @data_used ELSE @log_used END 

Query Source and answer to similar question

By the way, it is usually good idea to split data file in your tempDB into number of equally sized chunks (number depends on the number of cores). It tends to help performance a lot. Just don't split the log file (it will do more harm than good).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.