3

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:      From accepted answer

vs.

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)

  • Please post the query used, the output, and a screen capture of the actual file size in object explorer. These will all match if the query is correct. The only time they would not match would be if it's a sparse file or if the folder is compressed (which isn't supported). – Sean says Remove Sara Chipps Jun 23 '16 at 19:38
  • I've updated my answer to include a calculation to get the file size as it should be reflected by the disk manager. Look at the file's property to see what the file size is versus what the disk manager allocates for the file (as shown in the folder view). – John Jun 23 '16 at 21:29
  • @SeanGallardy, posted screen images. No folder compression. sys.database_files says is_sparse = 0. – crokusek Jun 23 '16 at 22:04
7

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

  • This is accurately reflecting the size shown by file explorer. Hope someone will comment on why the original queries are not always correct. – crokusek Jun 23 '16 at 21:31
  • Me too, but I think it will boil down to SQL storing data in 8KB pages versus file system allocation units, the latter one which can be changed. So, this answer I believe would be specifically for those drives formatted with 4KB units, which is the default, which would make this answer applicable to machines who have the allocation units set to the default. I would believe that many SQL DBAs have at least tried larger allocation units. – John Jun 23 '16 at 21:46
  • I am also going to modify this answer since I've since read that sys.sysfiles will likely be deprecated. The changed SQL does return the same information and the same file size values as the original query. – John Jun 23 '16 at 21:48
  • I changed the rounding of digits to 3. Since the file manager usually shows only 2, it should be correct. At least mine is :) – John Jun 23 '16 at 21:53
  • In the updated answer the file_size is slightly low. Needs to adjust by 1.048576 = (1024 / 1000)^2. – crokusek Jun 23 '16 at 21:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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