• We have a database where one of the business group decided to remove several millions of rows as part of their upgrade process. (we removed for them..).. This database is partitioned based on a date and removing all these rows would help us gain around 30% of the DB size back. But obviously, if we run the shrink command, there is a possibility of the database getting fragmented and running rebuild obvious increases the space of the database. Is there any better way of reclaiming the space other than moving to a different file group as suggested by Paul Randall?.
  • If we go via the traditional shrink and rebuild index route to reclaim the space on several of these partitions, we will be having to put the db in simple (bulk did not help much with reindex ) to avoid the transaction log from getting filled. This obviously will break the log shipping and we will have to setup the logshipping on this VLDB database back again which would be another tedious work. For a VLDB, what would be the best way to reclaim space for few of these tables without breaking the logshipping and using the existing commands?

Thanks for your help with this

  • Will the database grow again? If yes, then what is the point of reclaiming the space temporarily? Are you going to lease it out short term? Apr 15, 2013 at 21:34
  • Why is the filegroup move option unpalatable? Apr 15, 2013 at 21:57
  • No..The partition where the space will be reclaimed is for older dates and data wont grow again there. Filegroup option is tedious process and requires especially for the size of the database we have. Appreciate your comments
    – DBAuser
    Apr 16, 2013 at 12:51
  • 1
    Aaron, yes the DB will grow again but not on the older partitions. The large space we got when deleting the data was in the older partitions and yes, it is a lot.
    – DBAuser
    Apr 16, 2013 at 13:01

3 Answers 3


I recently had a similar project where we implemented data compression on a partitioned table and reduced our space usage, resulting in the need to shrink down the files. We did the following to manage this while keeping the database online:

  1. Off hours DBCC SHRINKFILE executions. We would start the shrink file off production hours and cancel it before the next day's worth of production started. This would go on for several weeks, but progress on the shrink was not lost (as pages are getting moved towards the "front" of the file and aren't rolled back). This did cause fragmentation, but the fragmentation was acceptable to our business in order to keep the database online during file cleanup.

  2. Rebuilding individual partitions for the indexes. If your table is partitioned, my view is that you should be doing this anyway because it minimizes the impact of the rebuild. While we were doing file shrinks, we only did weekly index maintenance (we usually do daily) in order to work with/around the DBCC SHRINKFILE. Once we had reduced the filesize, we went back to daily index rebuilds. In order to rebuild the individually fragmented partitions, we leveraged Ola Hallengren's scripts, but the essential command is ALTER INDEX foo ON bar REBUILD PARTITION=n. You would run this command for each partition (n) that you wanted to rebuild.

This was a multi-week process for us, but we reduced a 1.5 TB file down to ~500 GB and did not interrupt the database service to do it.

  • Thanks. This is exactly what are currently doing. But we noticed that the space we gained from the older partitions during the shrink operation were used by the index rebuild in those older date partition. So now, we have that extra space in each of those older partition which is such a waste. I understand if we are going to use the space in the future and hence there is no need to shrink the DB again. The idea was to reclaim the space on the older partitions. What are your thoughts on the logshipping question? Thanks for your time and help
    – DBAuser
    Apr 16, 2013 at 12:56
  • The method I described above won't break your log shipping, but log management becomes a larger question depending on how frequently you take log backups and ship them. We did not experience excessive log growth with the above and we take log backups every 15 minutes (no log shipping). I'd question how/why your older partitions are being rebuilt if there's no change. How do you do your index rebuilds?
    – Mike Fal
    Apr 16, 2013 at 15:22
  • Thanks Mike. Lots of data were deleted from the older partition. To free up that space, When we did shrink of the older partition(s)/filegroups, obviously the FG's got fragmented to 90%. So we had to do a index rebuild which made the log size grow very big and cause the tran log to almost fill in bulk model. We do have trean log backups every few seconds but yet, the size of the tran file was too big. To minimise the issue with tran logs, we changed to simple to help with the index rebuild which broke the logshipping. We have it running now but was wondering what if db was 5TB database. Thx
    – DBAuser
    Apr 16, 2013 at 16:34
  • I would wager your issue is that you're rebuilding your index in one complete transaction. If you do a partition by partition rebuild in separate transactions, your log file should stay manageable. Again, I recommend Ola's scripts for handling this. Michelle Ufford's script will also work and is easier to read to see how it's done.
    – Mike Fal
    Apr 16, 2013 at 17:10
  • Nope. Did not do one transaction. Rebuild an index on a partition is one transaction unless you know of a way to split that transaction? So basically, one of these partitions was huge with lot of data and hence the log was filled up...I really appreciate your comments
    – DBAuser
    Apr 16, 2013 at 19:10

Based on the requirements that you've stated, shrinking the database then doing a rebuild is the way to do. The other option would be to simply leave the space as whitespace within the database to be used by new data.

  • The whitespace is created in the older partition for the years 2011 and 2012 which would never grow :-(. Hence the need to reclaim the sapce
    – DBAuser
    Apr 16, 2013 at 12:59
  • The whitespace are unallocated pages which can be used by any object in the database.
    – mrdenny
    Apr 16, 2013 at 17:04
  • Thanks. I would agree if it is for future partitions but the one we are reclaiming is are older partitions. That data is static and does not grow. Data can be deleted but no new data will be added
    – DBAuser
    Apr 16, 2013 at 19:12
  • When you deleted the rows from the table you should have ended up with a bunch of pages that became blank. Those pages would be reusable. If you didn't free up any pages, then the only way to get the data back would be to rebuild those partitions of the indexes. Then other partitions can use the now freed up pages.
    – mrdenny
    Apr 21, 2013 at 13:55

I have the same situation with several tables partitioned in a large database. Each partitioned table has a filegroup for each month and the filegroup contains the partition for that month. After the data is 3 months old the data cannot be change, so I am putting those filegroups into ReadOnly status. But before changing the status I reclaim space if the filegroup is less than 80% full. Below is the process I use.

  1. Run a query to get the allocated size, used size, calculate the percent used for each filegroup, along with the partitions within that filegroup.
  2. If less than 80% used shrink the filegroup to the used size plus 10%.
  3. Alter the filegrowth for the filegroup file to the same amount used to shrink the filegroup. Note: This is because the original filegrowth was large and when rebuilding the indexes the filegroup will try to grow.
  4. Rebuild all indexes for the partitions within that filegroup.
  5. Put the filegroup into ReadOnly status.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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