Search code examples
sqlt-sqltempdb

Is there a reason why the Growth value for tempdb must be divided by 128?


I am selecting the name, size, growth, maxsize, and physical_name from sys.master_files and sys.database_files. I've noticed that when I do this the growth value will be reported as 8192MB on both queries. When looking at tempdb in file properties SSMS UI, it shows its actual growth 64MB. So I noticed I could divide that by 128 and get my intended answer. But why? It also seems to only be doing this on Servers with additional tempdb files, such as numFiles == numProcessers <= 8.

Here's the T-SQL I am currently using to accomplish it:

--Gets tempdb initial size from sys.master_files
SELECT name
,((size*8)/1024) [InitialSizeInMB]
, growth
, CASE WHEN (MAX_SIZE = -1) THEN 'Unlimited' ELSE CAST(MAX_SIZE / 128.0 AS VARCHAR(20)) END as [MaxSize]
, physical_name AS CurrentLocation  

FROM sys.master_files  

WHERE database_id = DB_ID(N'tempdb');

--Gets tempdb current size from sys.database_files

select name
, ((size*8)/1024) [CurrentSizeInMB]
, growth
, CASE WHEN (MAX_SIZE = -1) THEN 'Unlimited' ELSE CAST(MAX_SIZE / 128.0 AS VARCHAR(20)) END as [MaxSize]
, physical_name AS CurrentLocation  
FROM Tempdb.sys.database_files
ORDER BY name desc;

Solution

  • The size, growth etc columns from sys.master_Files represent units of 8-KB pages, rounded to the nearest 64 KB

    Therefore the value when shown in units of megabytes is 64, which is (8 * 8192) / 1024 - and equally 8192 / 128.