And, sorry... I have to say that routine shrinking of files or databases is a total waste of time. 😉 Doing it for reasons of recovering space after a "rampant growth accident" might also the wrong thing to do if you don't do it correctly and, it looks like you might be doing it incorrectly. If you pack everything down and then rebuild your indexes, you will have wasted space equal to about 120% of your largest pagecount index and that last shrink with TRUNCATE ONLY won't take it (unwanted free space) out because your index rebuilds build a new copy of the index at the logical end of the file (after a shrink) because there's no room at the beginning of the shrunk file. If the goal really is to recover disk space, then you need to create a new temporary file group, rebuild your largest index(es) to that (moving the index using the DROP EXISTING option), do your first shrink, do all your other rebuilds not once but twice to avoid the end-of-file problem, and then rebuild the largest index back to that original file group and drop the temporary file group. Again, that's just a suggestion at this point... I'm not done experimenting with all this and it could be several months (if at all) before I have a demonstrable example.
Jeff
Hopefully your comments may help others, in our case it's not about recovering disk space, it's about removing as much fragmentation as possible. We don't truncate the data file initially as we know we will reuse the space when we rebuild, the second shrink where we just truncate is purely a bonus as the database is only used for reporting. However can you provide more detail on the 'end-of-file problem' you mentioned above. I'm presuming the twice refers to the (shrink and rebuild), and not just the (rebuild)?
pcd
Based on what you're saying, shrinking is probably the wrong thing to do for this. You're just trying to defragment indexes, correct? Before I can make additional recommendations or answer the "end-of-file-problem", could you tell me the total size of the database and what the size of the 4 largest tables (including the indexes) are, please?
And Gail is correct. In many cases (especially on mature databases where REORGANIZE is frequently used), people forget to rebuild stats and rebuilding all of the indexes did a lot of that for you, although it doesn't take care of all statistics (particularly, column stats).