Querying True Physical Database File Sizes

Question

On querying database file sizes...

1) Most if not all of these answers do not always match the physical file size shown in file explorer:

https://stackoverflow.com/questions/5945360

The accepted answer reports:      

vs.

2) This post does seem to match physical file size shown in file explorer:

    

Is there a query that will report the physical size without relying on dm_os_performance_counters? Are the first set of queries ignoring free space?

Actual size reported by File Explorer:

    

Why does Tempdb show a discrepency in both the data and log file sizes as shown?

Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)

Answer 1

Here you go. You can use this to get file sizes by file and filegroup. It includes the system file size and how much space it's taking on disk, as well as the SQL file size, SQL space used, and by extension SQL free space as well. It includes the full path of the file being evaluated.

SELECT fg.data_space_id AS FGID,
   (f.file_id) AS File_Id,
   -- As provided by OP, size on disk in bytes.
   CAST(f.size AS FLOAT) * 8.00 * 1024 AS Size_On_Disk_Bytes,
   ROUND((CAST(f.size AS FLOAT) * 8.00/1024)/1024,3) AS Actual_File_Size,
   ROUND(CAST((f.size) AS FLOAT)/128,2) AS Reserved_MB,
   ROUND(CAST((FILEPROPERTY(f.name,'SpaceUsed')) AS FLOAT)/128,2) AS Used_MB,
   ROUND((CAST((f.size) AS FLOAT)/128)-(CAST((FILEPROPERTY(f.name,'SpaceUsed'))AS FLOAT)/128),2) AS Free_MB,
   f.name,
   f.physical_name
FROM sys.database_files f 
    LEFT JOIN sys.filegroups fg
    ON f.data_space_id = fg.data_space_id

For more information on the difference between 'Size_On_Disk_Bytes' and 'Actual_File_Size', go here: https://superuser.com/questions/66825/what-is-the-difference-between-size-and-size-on-disk

Thanks