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

Question

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;

Thanks for making that more presentable. As far as my question goes though, I am more interested why the GROWTH value returns as 8192MB instead of 64MB? — Tanner
– Tanner, Commented Feb 1, 2023 at 19:11
The value does not show 8192MB, it shows 8192 pages, did you actually read the documentation? As for divide by 128, that's mathematically the same as multiply by 8 and divide by 1024 to convert kilobytes to megabytes. — Stu
– Stu, Commented Feb 1, 2023 at 19:15
Oh okay that makes sense, I'm still learning and I have read it many times. I just didn't process what it was saying very well. Thanks for writing out why that is and sorry if I wasted any of your time. — Tanner
– Tanner, Commented Feb 1, 2023 at 19:23

Stu · Accepted Answer · 2023-02-01 19:25:44Z

0

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.

answered Feb 1, 2023 at 19:25

Stu

32.7k6 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related