Reclaiming space from a large partitioned database

Question

• 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

Answer 1

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.

Answer 2

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.

Answer 3

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.