Rebuilding index has impact on mdf/ldf files SQL Server 2008

Question

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?

Answer 1

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 and BULK_LOGGED recovery models (SIMPLE would be the same as BULK_LOGGED -- in other words, minimally logged).

Answer 2

When you rebuild an index the database engine creates what is called an "intermediate sort result." These intermediate results are like chunks that are combined to become your index and creating these chunks requires space. If it can the database engine will actually sort the index in memory; but if not it will store the intermediate results in the destination file group.

Using the SORT_IN_TEMPDB option asks SQL Server to store these intermediate results in TempDb instead of rebuilding them in the source database. When the TempDb resides on a different disk to the source database this can help the performance of the rebuild task and can reduce the performance impact on your database.

A requirement of rebuilding indexes is that you must have sufficient space (wherever you decide to build the index) to hold the largest index in your database: this in your example is 25GB. Which, if you use TempDb you should quite easily have.

As to whether your source database will grow, well I wouldn't rule it out but it won't grow anywhere near to what it would if you rebuilt the indexes within it. If you are concerned about this fact then take a copy of the database and put it in a test environment and try it out - it is the only way to really know for sure.

SORT_IN_TEMPDB:

http://msdn.microsoft.com/en-us/library/ms188281.aspx

I hope this helps you.