BACK

0
Posted by albydarned 3 years ago
Credit: https://blog.sqlauthority.com/2016/06/07/sql-server-much-free-space-database/

SELECT
   SUBSTRING(a.FILENAME, 1, 1) Drive,
   [FILE_SIZE_MB] = convert(decimal(12, 2), round(a.size / 128.000, 2)),
   [SPACE_USED_MB] = convert(decimal(12, 2), round(fileproperty(a.name, 'SpaceUsed') / 128.000, 2)),
   [FREE_SPACE_MB] = convert(decimal(12, 2), round((a.size - fileproperty(a.name, 'SpaceUsed')) / 128.000, 2)),
   [FREE_SPACE_ % ] = convert(decimal(12, 2), 
   (
      convert(decimal(12, 2), round((a.size - fileproperty(a.name, 'SpaceUsed')) / 128.000, 2)) / convert(decimal(12, 2), round(a.size / 128.000, 2)) * 100
   )
),
   a.NAME,
   a.FILENAME 
FROM
   dbo.sysfiles a 
ORDER BY
   Drive,
   [Name]