November 18, 2008 at 5:01 am
Hi All,
I would like to know that after rebuilding all the indexes in a database.
Will data file (.mdf) grow?,
I know that while rebuilding the indexes log file (.ldf) will grow.
One gentleman told me that Yesterday there was rebuild index job then .mdf file grown then we had a space issue.
Rajesh Kasturi
November 18, 2008 at 5:04 am
In case, if you are changing fillfactor to lower digit surely mdf file will increase. e.g. current fillfactor is 0 or 100 and you define it to 80 surely mdf file will increase by 20%
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
November 19, 2008 at 2:51 pm
Hi there! In general, the answer is "yes," but it really depends on what options you specified during your index rebuild. Can you post a sample of your SQL here?
Did you specify SORT_IN_TEMP = ON? If not, then the temporary space required to rebuild the index was taken from the user database. Check out this Microsoft white paper for more information: http://download.microsoft.com/download/D/9/4/D948F981-926E-40FA-A026-5BFCF076D9B9/TEMPDB_Capacity_Planning_Index.doc
HTH!
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
November 19, 2008 at 4:20 pm
Very likely, because the index rebuild works by creating a complete new index before dropping the old one. So unless you have a fair amount of free space within the DB, the DB will have to grow to accommodate the index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 19, 2008 at 4:49 pm
Gail has it correct. It will use more space, because it rebuilds things, but MDF files will grow only if you run out of space inside the current MDF files (And you have autogrow set on). If autogrow is off, and you run out of space, the rebuild will fail.
January 22, 2009 at 12:50 am
Hi All,
Thanks for your reply, While rebuilding the index log file will be used or data file will be used.
I understand that it will use only log file not data file.
Rajesh Kasturi
January 22, 2009 at 3:52 am
Rajesh kasturi (1/22/2009)
Thanks for your reply, While rebuilding the index log file will be used or data file will be used.
Both. The data file because that's where the new index will be written to. The log because it's a data change and hence needs logging.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2009 at 1:37 pm
That is correct, however, how do you explain the growth of the data file when there is enough of free space within the existing mdf.
The database size is 46GB, the data file is 42 GB (17GB + free space), the log is 4Gb. The index size (total) is 650MB. The space required for the rebuild is usually 1.5 x (Current Index Size) which puts me around 1 GB. I had the data file grow over 261 times with 50MB increments (12GB) during the last reindex.
The Sort_In_Tempdb is set to ON.
Any ideas?
February 9, 2009 at 1:44 pm
SA (2/9/2009)
That is correct, however, how do you explain the growth of the data file when there is enough of free space within the existing mdf.The database size is 46GB, the data file is 42 GB (17GB + free space), the log is 4Gb. The index size (total) is 650MB. The space required for the rebuild is usually 1.5 x (Current Index Size) which puts me around 1 GB. I had the data file grow over 261 times with 50MB increments (12GB) during the last reindex.
The Sort_In_Tempdb is set to ON.
Any ideas?
And all 12GB happened with just that *one* index of 650MB ?
* Noel
February 9, 2009 at 2:07 pm
Not really, here's the break up
340CLUSTERED
327HEAP
474NONCLUSTERED
The DB size on 2/1 was 46GB and the 12GB growth was over the duration of the reindex. So someone or some process had to shrink the db. :angry:
February 9, 2009 at 2:31 pm
SORT_IN_TEMPDB reduces the size needed on the destination because the "sort runs" and "mapping indexes" (and version store if online) are not allocated on the destination.
Now, IF you rebuild ALL your indexes EVERY TIME you should redo your plan to only affect those that actually need it!
* Noel
February 9, 2009 at 2:34 pm
I'm currently using the default database reindex plan.
Do you mean using some custom scripts for the reindexing? Can you point me to some scripts that are out there as a starting point?
February 9, 2009 at 2:42 pm
Here is one of the *many* :
* Noel
February 9, 2009 at 2:53 pm
February 9, 2009 at 2:58 pm
Here's a link to my version. There's a Standard and an Enterprise version, but these will only run on SQL 2005 or 2008:
http://sqlfool.com/2008/11/updated-index-defrag-script-2005-2008/[/url]
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply