Estimate impact needed for rebuilding all indexes?

Question

Have a 3rd party system that has a job that rebuilds all indexes nightly. We get periodic alerts when this happens that the TLog isnt big enough or a backup fails because the TLog is full. I know that rebuilding an index, and especially rebuilding ALL indexes for a large Db with several large tables, some of the indexes alone are over 1GB. How can I get a rough idea of the sizing needed for the Transaction Log(s) for the impact that the index rebuild will need? What about TempDb?
I realize there are many factors but where to start? One thing I consider is the total size of all the indexes. How can I use that number in estimating what the Tlog size should be to accommodate an index rebuild job?

Answer 1

Summarize the size of all indexes (including the clustered indexes for the tables that has clustered indexes). This will give you a rough idea about the space usage requirements for logging the operations. And work with that 3:rd party so they don't blindly rebuild all indexes! :-)

Answer 2

I know that rebuilding an index, and especially rebuilding ALL indexes for a large Db with several large tables,

I understand that you know the disadvantages of rebuilding all indexes but I would reiterate that you are doing wrong. You MUST only rebuild indexes which are

1. Fragmented > 30 %
2. Having page_count > 2000

Small indexes must be ignored.

• How can I get a rough idea of the sizing needed for the Transaction Log(s) for the impact that the index rebuild will need?

Did you got chance to read transaction-log-disk-space-for-index-operations and disk-space-requirements-for-index-ddl-operations I guess it has details about what you are asking. If you really want to do hard math see index-disk-space-Calculation-example. The last article has basically calculation/example to show how to get rough idea about disk space requirement for index.

PS: There is no specific article but if you know your disk size of index and use sort_in_tempdb option

Answer 3

Hi @bitshift ,

Rebuilding an index needs enough space to create the new index. A simplified rule of thumb seems to be that you need about 120% of the space used by the original index. This may be in the database or in tempdb, depending on whether SORT_IN_TEMPDB is ON or OFF. If possible, have SORT_IN_TEMPDB = ON this will reduce some of the logging that is done.

Refer to TRANSACTION LOG CONSEQUENCES ON REBUILDING SQL SERVER INDEXES to get detail information.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.