1

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

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.

Improve this question
3
  • 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. Commented Jun 23, 2016 at 13:51
  • Neat trick. Thanks for that. But do you have any idea why the results differ? Commented Jun 23, 2016 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. Commented Jun 23, 2016 at 14:42

1 Answer 1

Reset to default
1

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.