April 11th, 2011:

Here’s a quick script that will allow you to gather up your free space by drive on your server. You can use this to evaluate your space usage and address immediate concerns. It’s a straightforward query that uses dynamic SQL to query the files in each database’s sys.database_files view and the SpaceUsed property collected by the FILEPROPERTY function.

declare @return table
(drive_letter varchar(100),
 allocated_space decimal(15,2),
 used_space decimal(15,2),
 free_space decimal(15,2))

declare recscan cursor for
select name from sys.databases
where state = 0

declare @db varchar(100)
declare @sql varchar(max)

open recscan
fetch next from recscan into @db

while @@FETCH_STATUS = 0
begin
	set @sql = 	'use ['+@db+'] '+
				'SELECT	UPPER(SUBSTRING(filename,1,2)) ,'+
				'sum(a.Size)/128.0,'+
				'sum(FILEPROPERTY(a.Name,''SpaceUsed''))/128.0,'+
				'sum(a.Size-FILEPROPERTY(a.Name,''SpaceUsed''))/128.0'+
				'FROM ['+@db+'].dbo.sysfiles a ' +
				'GROUP BY SUBSTRING(filename,1,2)'
	insert @return
	exec(@sql)

	fetch next from recscan into @db
end

close recscan
deallocate recscan

select
	drive_letter,
	sum(allocated_space) total_alocated,
	sum(used_space) total_used,
	sum(free_space) total_free,
	sum(free_space)/sum(allocated_space) perc_free
from @return
group by drive_letter
order by drive_letter

The main trick of this and the reason why we can’t use sys.master_files is because FILEPROPERTY only provides data for the current database context. If you try to call FILEPROPERTY for a file not in your current database, it will return a NULL.  As a result, we need to dynamically switch database contexts and gather the data for all of our databases.