While working with multiple FileGroup/File combos for table partitioning and setting up for PieceMeal restores, I ran into the following problem as quoted from the MS article at http://technet.microsoft.com/en-us/library/ms179542.aspx
. The embolding is mine.
When a new index structure is created, disk space for both the old (source) and new (target) structures is required in their appropriate files and filegroups. The old structure is not deallocated until the index creation transaction commits.
This problem will easily cause the given File to double in size even if SORT IN TEMPDB = ON. Of course, when setting a File for something like an Audit table to READ ONLY and you have 1 of these files for each of more than four years, the amount of wasted disk space in each of those files really starts to add up. Essentially, you're doubling the disk space required for a huge table.
Now, I've figured out a way to keep that from happening each month when I go to set the previous month's file to READ ONLY (it would be a problem even if I didn't set it to READ ONLY, BTW), but it's complex, offline, and reasonably slow because it requires that you copy the data out of the file to another FileGroup/File, SWITCH the original data to another table on the same File/FileGroup, dropping the SWITCH table, shrinking the bloated file I just moved the data out of to 0, calculate the size of the data and indexes of the copied data, resize the previously bloated file using that number so as to avoid fragmentation caused by miniscule growth factors, and then reapply the partitioned CI to the moved data to overcome the problem of the original index staying active on the file group until the new index is created.My question is
, does anyone have a trick up their sleeve that would make rebuilding a clustered index on a single file partition any easier and without the doubling in size due to the delayed deallocation problem cited at the URL I posted at the beginning of this post? Or am I pretty much stuck with the relatively complex method I've developed for this?
As a bit of a sidebar on this subject, I'd love to ask MS "What were you thinking?" Peter Norton got this type of stuff down pat literally decades ago.
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Helpful Links:
How to post code problemsHow to post performance problemsForum FAQs