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:


The accepted answer reports:      From accepted answer


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

    enter image description here

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:

    enter image description here

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)


1 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,
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



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.