Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
  • 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

share|improve this question
    
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? –  Aaron Bertrand Apr 15 '13 at 21:34
    
Why is the filegroup move option unpalatable? –  Mark Storey-Smith Apr 15 '13 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 '13 at 12:51
    
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 '13 at 13:01
add comment

2 Answers

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.

share|improve this answer
    
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 '13 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 '13 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 '13 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 '13 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 '13 at 19:10
add comment

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.

share|improve this answer
    
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 '13 at 12:59
    
The whitespace are unallocated pages which can be used by any object in the database. –  mrdenny Apr 16 '13 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 '13 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 '13 at 13:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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