Is DBCC SHOWFILESTATS an accurate way to see unused space? We have a few tables each with near 100 million rows of data.  A co-worker has these tables setup with partitions based on a date field.

Our disk space is starting to run low and knowing that partitions have their own file group, I wanted to see if some of the partitions were larger than they needed to be and were chewing up disk.

I ran the poorly documented command:

dbcc showfilestats


and here is a snippet of the output:

TotalExtents   UsedExtents   Name         FileName
363200         200701        LogJun2013   F:\SQL\DATA\logs_phyJun2013.ndf
812800         432127        LogJul2013   F:\SQL\DATA\logs_phyJul2013.ndf
718400         401500        LogAug2013   F:\SQL\DATA\logs_phyAug2013.ndf
1016983        555565        LogSep2013   F:\SQL\DATA\logs_phySep2013.ndf


If I am understanding this correctly (IANADBA), there is a lot of unused space in these files given the high number of unused extents.  In addition, we only collect new data so we will never have a need to grow these old partitions from 2013.

Is my understanding of this correct?  If so, how can I go about freeing up the space from these files?
You can use sys.dm_db_index_physical_stats to identify the size of your partitions and how much space is being used.  Use the page_count field to see the size of your partition.  If you perform a SAMPLED or DETAILED scan, you will also get a value for avg_page_space_used_in_percent, which will allow you to estimate how much of the space in the partition is actually being used.

SELECT
    object_name(di.object_id) [table_name]
    ,i.name
    ,di.index_id
    ,di.partition_number
    ,di.record_count
    ,di.page_count/128.0 [partition_size_MB]
    ,di.avg_page_space_used_in_percent
    ,(di.page_count/128.0) * di.avg_page_space_used_in_percent * .01 [space_used_MB]
FROM
    sys.indexes i 
    join sys.dm_db_index_physical_stats(DB_ID(),object_id('&lt;Table Name'&gt;), NULL, NULL, 'SAMPLED') di 
      on i.object_id = di.object_id and i.index_id = di.index_id


If you want to check the free space in the actual files themselves, I recommend using a query from Glenn Berry's DMV script.

SELECT f.name AS [File Name] , f.physical_name AS [Physical Name], 
CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],
CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2)) 
AS [Available Space In MB], [file_id], fg.name AS [Filegroup Name]
FROM sys.database_files AS f WITH (NOLOCK) 
LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK) 
ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);


This should give you a rough idea of whether or not you have any space to reclaim.  As for actually reclaiming it, it depends on what you need to do.  If your files are simply over provisioned, you can shrink them using DBCC SHRINKFILE (this is not something that should be done regularly).  If, however, the files are mostly full but you have free space within the partitions, try rebuilding the indexes to reclaim some of the space (likely lost due to fragmentation).  If the disk is full and there isn't space in the partitions, consider archiving data off to another disk system or to offline storage.

That all being said, 100 million rows really isn't that much data and I'm curious as to why partitioning was even chosen as a solution.  It's a lot of overhead without a lot of benefit to you.