locked
Disk space requirement for Rebuild indexes RRS feed

  • Question

  • Hi

    I am using SQL Server 2008 (RTM) Standard Edition.

    In my environment, one of my Database size is 75 gb and I have to create a plan for index rebuild using maintenance plan.

    But when we rebuild indexes, it requires some space on data and log files of database.

    Can anyone tell me that how can we calculate disk space requirement for index rebuild process ?



    Mark As Answer If My Reply Is Helpful<br/> Thanks<br/> Neeraj Bhandari (MCTS - Sql Server 2008)

    Thursday, July 9, 2015 8:08 AM

Answers

  • I am using SQL Server 2008 (RTM) Standard Edition.

    Really? You should apply Service Pack 4 with very little delay.

    In my environment, one of my Database size is 75 gb and I have to create a plan for index rebuild using maintenance plan.

    But when we rebuild indexes, it requires some space on data and log files of database.

    Can anyone tell me that how can we calculate disk space requirement for index rebuild process ?

    SELECT TOP 1 object_name(id), convert(bigint, reserved) * 8192 / 1000000
    FROM   sysindexes
    WHERE  indid IN (0,1)
    ORDER  BY reserved DESC

    This gives you the size of your biggest table in megabytes. You will need at least this much free space in the data file to rebuild the indexes for that table. If this free space is not available, the data file will grow according to its autogrow settings.

    How much log space you will need depends on your recovery model. If you have full recovery, I would guess that you need four times the size of the table, assuming that you take log backup directly before and after. With simple or bulk_logged recovery, the space consumption is less as the index rebuild is minimally logged.

    For index maintenance, I recommend that you look at http://ola.hallengren.com for something which is more intelligent than you have in SSMS.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, July 9, 2015 8:35 AM

All replies

  • I am using SQL Server 2008 (RTM) Standard Edition.

    Really? You should apply Service Pack 4 with very little delay.

    In my environment, one of my Database size is 75 gb and I have to create a plan for index rebuild using maintenance plan.

    But when we rebuild indexes, it requires some space on data and log files of database.

    Can anyone tell me that how can we calculate disk space requirement for index rebuild process ?

    SELECT TOP 1 object_name(id), convert(bigint, reserved) * 8192 / 1000000
    FROM   sysindexes
    WHERE  indid IN (0,1)
    ORDER  BY reserved DESC

    This gives you the size of your biggest table in megabytes. You will need at least this much free space in the data file to rebuild the indexes for that table. If this free space is not available, the data file will grow according to its autogrow settings.

    How much log space you will need depends on your recovery model. If you have full recovery, I would guess that you need four times the size of the table, assuming that you take log backup directly before and after. With simple or bulk_logged recovery, the space consumption is less as the index rebuild is minimally logged.

    For index maintenance, I recommend that you look at http://ola.hallengren.com for something which is more intelligent than you have in SSMS.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, July 9, 2015 8:35 AM
  • Hello,

    Please read the following article:

    https://msdn.microsoft.com/en-US/library/ms191183(v=sql.100).aspx

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Thursday, July 9, 2015 1:14 PM
  • It depends on a number of factors. 

    1) The time required is completely subjective, and no true method of estimating it is possible. 

    2) It depends on a number of factors such as Disk configuration, # IOPS, are you creating the index on the same disk array that the table data exists on, or is it going to a different disk array, how much memory do you have, can the sort operations be performed in memory, or will they sort in tempdb, what DOP level are your rebuilding at, can the system function optimally under this DOP level, how much other concurrent activity is happeneing at the time this is happening that could cause blocking during the operation is it being done ONLINE in Enterprise Edition or Offline in Standard Edition?  It all makes a difference.

    3) How many database size ?

    as  suggested Erland use  http://ola.hallengren.com.it will help ful.

    Refer


    Vote As Helpful if it helps to solve your issue

    Friday, July 10, 2015 7:14 AM
  • Hi Neeraj

    Space requirement depends how you are rebuilding the index.

    If you are rebuilding offline then a table would require adequate space on data file where it keeps copy of table. Then perform index creation.

    You need to make sure that data file has adequate space for largest table in your database assuming you are reuilding index seuentially table by table.

    If you opt parallel index rebuild then approximatelly you need double the free space what is occupied.

    Friday, July 10, 2015 7:37 AM