While rebuilding index have used option 'SORT_IN_TEMPDB = ON' in order to avoid unnecessary growing your user database files.
What does it exactly means?
Is complete process done on TempDB
and does not grow mdf/ldf
file size?
For example before reindex (database is in simple recovery mode), the largest index size in the database is 25GB
.mdf
size (100GB allocated and used –> no freespace).ldf
size (20GB)TempDB
has 200GB freespace
What will be the approximate size for .mdf
& .ldf
files. Are they grow or not?
Best Answer
This option only controls where intermediate sort space (not the index itself) is allocated from. See here for a more in-depth description of how
tempdb
is used when indexes are created.You still need enough free space in the data file(s) to accomodate a second copy of the index, because SQL Server must lay down the new index before dropping the old one (it doesn't overwrite in-place).
The amount of space needed in the log file depends on the recovery model of the database. Check out this blog post for a comparison between
FULL
andBULK_LOGGED
recovery models (SIMPLE
would be the same asBULK_LOGGED
-- in other words, minimally logged).