2

When rebuilding an index in SQL server, where in the datafile does it put that data?

Does it start at the beginning of free space and fill in the gaps from there?

Context: I have some large .ndf files I'm trying to shrink (not important why); but instead of using a SHRINKFILE command, I'm wondering if I can rebuild the indexes and then truncate the file from 80GB down to 25GB.

Edit: There are many reasons to want to reduce the file space or reorganize it logically. These may relate to fragmentation, how that fragmentation is translated to IO requests, moving large files across a network, swapping out storage space, how data is read sequentially, etc. Excessive space usage could even prevent other files from being written to your disk that need the space or dbs that need room to grow. You might have done some testing and now need to clean up excessive data and the space it required. Imagine you just switched to a newer database server and could take a 400GB database and compress it down to 100GB with newly available db management techniques, compression, etc, That's got value and I'm sure there are many other scenarios where this matters. Just be careful to judge the reasons behind questions please.

  • 3
    You know if you shrink the file it will grow again, right? – Aaron Bertrand Apr 25 '18 at 16:17
  • Yes, I'm well aware of that, and fragmentation, and moving data to other file groups as an option, etc. I mentioned to ignore those concerns above. Please be careful not to judge the merits of a question because it might suggest a bad practice. It kind of devalues it even if you mean well. – Middletone Apr 25 '18 at 16:39
  • Forgive me, but most people I come across (including future readers of this question) who are asking about shrinking files don't realize how expensive (and pointless) it is to shrink only to grow again. So it's not fair to just say "oh, don't worry about that, I know what I'm doing." Future readers don't. I'm not judging the question, I'm just making sure that's clear to anyone who comes across it, since the spirit of this entire network is to help more than just the single person asking the question. – Aaron Bertrand Apr 25 '18 at 16:51
  • I think most professionals are asking what for? Rather than why. Is there a problem you are solving? It’s not lack of interest, it is just that often this requires way more steps to implement for such little gain. Most problems are design issue, architectural technical debt that led to this. Also consider a Production Server, you would have to test on your Cert before going live...but what of refreshes from prod? I have a box where simply moving a number of large blob data would require 3-5hrs of downtime...if I only did this once! We decided to start from scratch and design right. 😄 – clifton_h Apr 25 '18 at 16:57
  • 1
    Who is "they"? Where do you see this? Questions shouldn't be books but they should contain enough detail that the problem can be understood and solved. If they don't include that detail, expect questions like mine. – Aaron Bertrand Apr 25 '18 at 17:01
4

I don't believe those details are documented anywhere, and even if you were to figure it out (by asking Paul Randal, or perhaps by mapping the exact pages before and after an index rebuild), you may not be able to rely on those going forward. You also might end up with some random single pages at the end of the file anyway, and still need to do a shrink.

The best idea, in my opinion, is to do an index rebuild into a different file group:

  • Create a new filegroup
  • Move all affected tables and indexes into the new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the tables and remove fragmentation from them at the same time
  • Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)

If that's not an option, then you can instead do a shrink-then-reindex, or maybe a reindex-shrink-reindex:

  1. Initial reindex: this releases free space within each page, and might be necessary if you've made major changes in the table structure like dropping large columns or changing column datatypes
  2. ShrinkFile (never use ShrinkDatabase): Pick a reasonable target size, shrink each file with some remaining free space (I usually pick 15-20% free)
  3. Final reindex: Resolves any fragmentation we created in step 2.

This multi-pass approach is a ton of IO, and might take a while to do. That's why the single-pass approach into a different file group is preferred, if you can pre-allocate the space.

Also, the final reindex may reclaim some of the space recovered in step 2. This is common where the majority of space is taken up by a single huge table or single huge index.

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.