February 11, 2009 at 2:48 pm
Thanks for all your responses.
I tried to execute the maintenance plan in a development environment and have been able to confirm the database grows from 26 gb to 70 gb during the reindex process.
original sizes
26gb (total)
25gb (data)
650mb (index)
500mb (free space)
100 mb (log)
post maintenance plan
70gb (total) - full recovery 48gb (total) - bulk recovery.
25gb (data)
650mb (index)
22gb (free space)
22 gb (log)
I used the script posted and
27gb (total)
25gb (data)
650mb (index)
1gb (free space)
500mb (log)
This is great since the db size hasn't grown out of control. However, I'm trying to verify that all the indexes with frag percent over 30% have been rebuilt since, when I re-run the query which is selecting the indexes to rebuild based on the DMF, it returns records indicating fragmented indexes. Have you seen this happen? I will do some more testing and keep you posted.
Meanwhile, what's I can't figure out is what's causing the maintenance plan to grow the database / log large? The sort_in_tempdb is set to on. I will test changing the recovery model to simple before kicking-off the reindex and then turn it back to full. Is this what the industry practice is? Does the fact that there are over 300 Heaps in the db be the reason for this growth, if so why?
Thank you...
March 23, 2009 at 1:45 pm
MAYDAY!!! MAYDAY!!!!
I have been fighting this issue for days.
I have limited disk space and I am trying to rebuild my indexes.
Here is my command
use [MY_DATABASE];
EXEC sp_MSforeachtable "SET QUOTED_IDENTIFIER ON; print ' ?'; ALTER INDEX ALL ON ? REBUILD with (sort_in_TempDB = on);"; ( YES I KNOW I SHOULD NOT BE USING sp_MSforeachtable please lets not get hung up on it right now )
My question is I thought if I set 'sort_in_TempDB' to on the rebuild would use tempdb and I should see TEMPDB grow and the .mdf for MY_DATABASE would not grow. What I am seeing is MY_DATABASE.mdf is growing, 2x's the size.
Why is this happening? Is there something I am missing? Is there anyway to prevent the .MDF and/or .LDF from growing?
March 23, 2009 at 1:51 pm
Look for the largest table / clustered index. I figured that my largest table is 22GB and has a clustered index so in order to rebuild it each week, the database will grow over 1.5x 22GB atleast. This I believe may be more if SQL Server is rebuilding indexes in parallel.
Look at the page count for your indexes and I would worry about fragmentation for those which have over 1000 pages.
It will be interesting to see what the others have to say about this.
March 23, 2009 at 2:10 pm
Thanks for the quick response.
Can anybody explaine to me why MY_DATABASE.MDF is growing if I have 'sort_In_TempDB' set to on? I thought setting 'sort_in_TempDB' on tells SQL Server to use TEMPDB for temporary storage.
Thanks to everyone for you help.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply