Steve Jones - SSC Editor (8/9/2013)
Jeff Moden (8/8/2013)
Right sizing log files and datafiles for the FULL recovery model is a far bigger PITA than TempDB will ever be, IMHO. For example, even if you tell the system to "Sort In TempDB" when you go to do a clustered index rebuild on a very large table and you have Point-In-Time log backups running every 10 minutes, your log file can grow to several times the size of the original table during the process for a multitude of reasons. That's not to mention the bloat that it will cause in the MDF file while it makes a copy of the index.Of course, there's the fragmentation nightmare that occurs if you try to shrink a database.
All in all, there are some serious shortcomings that MS has continued to ignore on index maintenance over the years resulting in Balloons that appear to have check valves in the in the inflation tubes.
Sounds like a good article (hint, hint) :hehe::w00t:;-):-P
It's a complex subject so it might take a while to put together but an article on the subject is a good idea. I might just take that on and include snippets of the real life partitioning example that I've been writing code for to help alleviate such problems.
--Jeff Moden
Change is inevitable... Change for the better is not.