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