SET NOCOUNT ON

SELECTdf.name AS LogicalFileName

, ISNULL(fg.name, 'Log') AS FilegroupName

, physical_name AS PhysicalOSName

, (df.size / 128) AS SizeMBs

, (FILEPROPERTY(df.name, 'SpaceUsed') / 128) AS SpaceUsedMBs

, CASE df.growth

WHEN 0 THEN 'No growth'

ELSE 'Growth allowed'

END AS GrowthAllowed

, CASE

WHEN df.max_size = -1 THEN 'Unlimited'

WHEN df.max_size / 128 = 0 THEN CAST(df.size / 128 as varchar(50)) + ' MBs'

WHEN df.max_size / 128 = 2097152 and df.growth = 0 THEN 'No growth'

WHEN df.max_size = 268435456 THEN '2TB'

ELSE CAST(df.max_size / 128 AS VARCHAR(10)) + ' MBs'

END AS MaxGrowthSize

, CASE df.is_percent_growth

WHEN 0 THEN CAST(df.growth / 128 AS VARCHAR(10)) + ' MBs'

ELSE CAST(df.growth AS VARCHAR(10)) + ' %'

END AS Growth

, (df.size / 128) - (FILEPROPERTY(df.name, 'SpaceUsed') / 128) AS FreeMBs

FROM sys.database_files df LEFT OUTER JOIN sys.filegroups fg ON df.data_space_id = fg.data_space_id

ORDER BY df.type

If you move objects to a secondary FG from the primary FG you will see increased free space in the primary FG but it's not released back to the OS