For tempdb:
- sys.master_files = shows file sizes at startup
- sys.database_files = shows current file sizes
/* sys.master_files*/
SELECT
name
,physical_name
,type_desc
,(size * 8) /1024 AS size_mb
FROM tempdb.sys.master_files
WHERE DB_NAME(database_id) = 'tempdb'
ORDER BY name DESC;
/* sys.database_files */
SELECT
name
,physical_name
,type_desc
,(size * 8) /1024 AS size_mb
FROM tempdb.sys.database_files
ORDER BY name DESC;