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?

  • What version of SQL Server? – Shawn Melton Jul 3 '14 at 2:38
  • @ShawnMelton SQL Server 2008 R2 – Justin Helgerson Jul 3 '14 at 2:52
  • Quick math tells me that you have ~28GB free on your largest filegroup (and I'm just eyeballing and saying that you have ~60GB total unused disk space). There are many issues here: 1) partitioning isn't being properly used so there may be a better solution than "shrink a file", but we'd need to know your schema, 2) I'm concerned about the location of the files (all on the same disk/mountpoint), 3) even if you do shrink all of the files, how are you planning on "reclaiming" this space? – swasheck Jul 3 '14 at 4:02
  • @zlatan I appreciate the reply. I'm not actually in favor of our partitioning (simply because it's adding complexity), but, unfortunately I'm stuck with it until I can convince otherwise (I'm more of a web/ops guy). 1) What could be better or do you have a reason to suggest not using partitioning? 2) Are you saying the partitions should be scattered across multiple drives? 3) I don't mind if SQL Server holds onto the space so long as it can be used for future growth (which will all be new data, not old in terms of date). – Justin Helgerson Jul 3 '14 at 4:13

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.

    object_name(di.object_id) [table_name]
    ,di.page_count/128.0 [partition_size_MB]
    ,(di.page_count/128.0) * di.avg_page_space_used_in_percent * .01 [space_used_MB]
    sys.indexes i 
    join sys.dm_db_index_physical_stats(DB_ID(),object_id('<Table Name'>), 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.

  • this is almost exactly the answer i was going to post - thanks. @Ek0nomik, if you want help tearing down partitioning, let us know. – swasheck Jul 3 '14 at 14:21
  • Good answer, thanks. Partitioning was chosen because someone with a higher pay grade than me decided to use it. :) @zlatan For my own interest, I'd love to hear why tearing down partitioning makes sense. While I likely can't change it in the short term, long term I may have some options. – Justin Helgerson Jul 3 '14 at 15:28
  • @Ek0nomik sure ... pop on into the heap to discuss – swasheck Jul 3 '14 at 15:38
  • 1
    For more information, I've blogged a couple of times about partitioning. This post is about my first partitioning implementation, this post is about how partition elimination works (one of several reasons for why partitioning gets implemented). Frankly, I'd be asking that higher pay grade person to explain why (non-confrontational-y) partitioning is being used. As stated, it's a lot of management overhead and may not be a good fit. – Mike Fal Jul 3 '14 at 15:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.