[DMV] 파일 그룹별 전체 크기와 할당 및 사용하고 있는 공간 확인

select   a.name

,        b.size * 8 / 1024 as 'PhysicalSize(MB)', (b.size - sum(total_pages)) * 8 / 1024 as 'UnAllocationSize(MB)'

,        sum(total_pages) * 8 / 1024 as 'TotalAllocation(MB)', sum(used_pages) * 8 / 1024 as 'UsedAllocation(MB)'

,        sum(data_pages) * 8 / 1024 as 'DataSize(MB)', sum(total_pages - used_pages) * 8 / 1024 as 'FreeSize(MB)'

from              sys.filegroups a with(nolock)

    inner join (  select data_space_id, sum(size) as 'Size'

                    from sys.database_files with(nolock)

                    group by data_space_id ) b                on a.data_space_id = b.data_space_id

    inner join    sys.allocation_units c with(nolock)         on a.data_space_id = c.data_space_id

group by a.name, b.size

order by 1, 2