0

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;
Thom A
  • 88,727
  • 11
  • 45
  • 75
Tanner
  • 3
  • 2
  • 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 Feb 01 '23 at 19:11
  • 1
    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 Feb 01 '23 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 Feb 01 '23 at 19:23

1 Answers1

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.

Stu
  • 30,392
  • 6
  • 14
  • 33