none
Rebuild of index is taking lot of disk space and filling the User Database Log File very rapidly.

    Question

  • Hi,

    My name is Phani, i work as a DBA for small firm, i have a question related to index rebuild maintenance plan which is scheduled for weekly once, and whenever the job runs the disk space where the log file exist is getting filled up rapidly and filling up the log file of the USER DATABASE very rapidly. I am rebuilding the indexes using the options, ONLINE=OFF and SORT_IN_TEMPDB=OFF. i am really not sure what is going wrong and where is it taking more space. Believe me once the job gets completed my log file of the user database will be 0.5TB.

    Any help would be greatly appreciated.

    Thanks & regards,

    Phani

    Wednesday, August 13, 2014 11:46 PM

Answers

  • Hi,

    It is hard to remotely support without the option to monitor things myself :-) let's try to get some more relevant information from you. For this reason I can't recommend what to do especially on production! Therefore everything here is only options that you should use on development environment, on your own risk.

    >> what is the size of the biggest index ?
    >> how many indexes do you have?
    >> what is the average size of the indexes
    >> If the number of indexes is not high then you can just post a table of: index-name, index-size

    >> parallel index operations are only permitted in Enterprise Edition. This is a different from your old version.
    >> during the process, how many CPU works?
    >> can you try to use maxdop or any other way to make sure that only 1 cpu working (play with option a bit) ?

    >> Can you rebuild "index by index" in different times, while the log backup come between those operation (this will limit the log growth a bit), same with data backup...

    >> You can use Extended Events to capture each statement executed (sp_statement_completed event), and the actual query plan for the statement (query_post_execution_showplan event), in order to monitor.
    *** it is NOT recommended to capture the query_post_execution_showplan event against a live, production system!


    [Personal Site] [Blog] [Facebook]signature

    Thursday, August 14, 2014 11:01 AM

All replies

  • When you build an index, then SQL Server allocates disk space for the construction of the new index, and in addition it allocates a place for the benefit of sorting the records as an intermediate step before creating a new index. If the old index and the new index in the same Filegroup, then the file will probably grow to accommodate this other place. Only after the construction of the index is complete, SQL Server releases the place of the old index. If the original file size was 60GB. and the biggest index is about 30 GB then the arrival of about 100 GB.

    Now let's move to solutions ... 

    >> First of all the best thing to do is to allocate enough disk space indexes and their building again, and then the files do not need to grow, and no one will get upset from that disk space runs out.

    >> By using SORT_IN_TEMPDB while re-constructing indexes at least part of the emergency will happen in tempdb, it will save small space in your file. On the other hand, it will make the allocation of much more space in tempdb, but if tempdb is on a different disk, and if it is not too busy as it is, then it might help a little.

    >> You can create two Filegroups, and each time you build a new index, build it on the second Filegroup. If the index is currently at Filegroup A, then when you re-build it, build it on Filegroup B. The next time you build it again on Filegroup A, and so on. Still the two files together occupy same size, as before, but right then you can do without any problem Shrink file containing the old index. Of course, this means constantly having to deal with growth and gathering of files.

    >> If the event log is also on the same disk, then during construction of indexes you can move the database Recovery Model from Full to Bulk-Logged Recovery Model, thus saving a lot of room to blog. You have to remember the disadvantage of restore point (most cases this is not recommended).

    >> On this occasion we should also mention the Fill Factor that could affect the size of the index while rebuilding. If it is too low without justifiable reason, then the index would be too large and will occupy more space on the disk. 

    I hope this is useful :-)


    [Personal Site] [Blog] [Facebook]signature

    Thursday, August 14, 2014 12:27 AM
  • Hi Ariely,

    Thank you so much for the reply, actually we didn't have this problem with the disk space before. Recently two weeks back we migrated from SQL 2005 Standard edition to SQL 2012 Enterprise edition, from then the problem with the index rebuild started. Can you please shed some light on this, I guess there might be some difference in the SQL 2005 Standard edition maintenance plans and the  SQL 2012 Enterprise edition maintenance plan.

    Because my main aim is to reduce the growth of the disk space, as I have tried all the above mentioned solutions except the third and fourth options,

    >> You can create two Filegroups, and each time you build a new index, build it on the second Filegroup. If the index is currently at Filegroup A, then when you re-build it, build it on Filegroup B. The next time you build it again on Filegroup A, and so on. Still the two files together occupy same size, as before, but right then you can do without any problem Shrink file containing the old index. Of course, this means constantly having to deal with growth and gathering of files.

    >> If the event log is also on the same disk, then during construction of indexes you can move the database Recovery Model from Full to Bulk-Logged Recovery Model, thus saving a lot of room to blog. You have to remember the disadvantage of restore point (most cases this is not recommended).

    As my aim is to reduce the disk space, so third option not helpful and the changing the recovery model is not recommended as we might loose some data.

    I have tried second and fifth options SORT_IN_TEMPDB option to ON and FILL FACTOR to 80, but they are also increasing the size of the log file of USER DATABASE and the disk space too.

    I am really facing this issue for the first time and not sure how to handle this :(

    Thanks & regards,

    Phani Kalipatnapu

    Thursday, August 14, 2014 9:47 AM
  • Hi,

    It is hard to remotely support without the option to monitor things myself :-) let's try to get some more relevant information from you. For this reason I can't recommend what to do especially on production! Therefore everything here is only options that you should use on development environment, on your own risk.

    >> what is the size of the biggest index ?
    >> how many indexes do you have?
    >> what is the average size of the indexes
    >> If the number of indexes is not high then you can just post a table of: index-name, index-size

    >> parallel index operations are only permitted in Enterprise Edition. This is a different from your old version.
    >> during the process, how many CPU works?
    >> can you try to use maxdop or any other way to make sure that only 1 cpu working (play with option a bit) ?

    >> Can you rebuild "index by index" in different times, while the log backup come between those operation (this will limit the log growth a bit), same with data backup...

    >> You can use Extended Events to capture each statement executed (sp_statement_completed event), and the actual query plan for the statement (query_post_execution_showplan event), in order to monitor.
    *** it is NOT recommended to capture the query_post_execution_showplan event against a live, production system!


    [Personal Site] [Blog] [Facebook]signature

    Thursday, August 14, 2014 11:01 AM
  • Hi Ariely,

    Thank you once again for your help, I really appreciate it, I am working with one option from above which is and that was my backup plan also,

    >> Can you rebuild "index by index" in different times, while the log backup come between those operation (this will limit the log growth a bit), same with data backup...

    Hopefully this should reduce my log space.

    Thanks & regards,

    Phani

    Thursday, August 14, 2014 11:25 PM
  • You are most welcome :-)

    [Personal Site] [Blog] [Facebook]signature

    Friday, August 15, 2014 8:12 AM