Find free space in a file group

Today morning the monitoring tool threw an error that one of the databases was 90% full. Space was needed to be added to that database. Since it had 54 data files spread across 10 file groups, the need was to find out which file group was running out of space. As a production support DBA this is one of the most common tasks. I use the below script to find out the space allocation on a database grouped by the file groups. The script makes use of the undocumented DBCC showfilestats command to get the space details.

declare @dbname varchar(256)
IF (OBJECT_ID('tempdb..#space') IS NOT NULL)
            drop table #space

IF (OBJECT_ID('tempdb..#filestats') IS NOT NULL)
            drop table #filestats

IF (OBJECT_ID('tempdb..#filegroup') IS NOT NULL)
            drop table #filegroup

create table #filestats
(fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(255),
filename varchar(1000))

create table #filegroup
(groupid int,
groupname varchar(256))

	insert into #filestats
	exec ('DBCC showfilestats with no_infomsgs')

	insert into #filegroup
	select  groupid, groupname
    from sysfilegroups

select g.groupname,
	sum(TotalExtents)*64.0/1024 as TotalSpaceMB,
	sum((TotalExtents - UsedExtents) * 64.0 / 1024.0) AvailSpaceMB
from #filestats f
join #filegroup g on f.filegroup = g.groupid
group by g.groupname

drop table #filestats
drop table #filegroup
set nocount off

The output of this script looks like this