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

https://technet.microsoft.com/en-us/library/ms176029(v=sql.105).aspx


  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



So here are my questions:


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


Any answer is greatly appreciated.
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);
CREATE TABLE #x(n SYSNAME, s DECIMAL(19,7), u DECIMAL(19,7), b BIT);
INSERT #x EXEC('DBCC SQLPERF(LogSpace);');
SELECT @log_used = u FROM #x WHERE n = DB_NAME();
DROP TABLE #x;

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
)
SELECT 
  file_type = t, 
  size = s,
  available = s-CASE t WHEN 'data' THEN @data_used ELSE @log_used END 
FROM x;


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