We are following vendor recommendations for a index rebuild changing the fillfactor and enabling data compression, on the test server the results have us wondering what is going on that would cause the TLog to exceed 650 GB, (ran out of drive space). Why is the tlog growing so much?

SQL Server Version 12.0.2456.0

Vendor code provided:

alter index all on dbo.ACC_LOG_DTL_IX rebuild with   
( data_compression=PAGE, fillfactor=85, online=ON)

Table properties

This is the script for the table and the one index.

    [ACCESS_INSTANT] [numeric](16, 6) NOT NULL,
    [PROCESS_ID] [varchar](254) NOT NULL,
    [DATA_MNEMONIC_ID] [varchar](15) NOT NULL,
    [STRING_VALUE] [varchar](2000) NULL,
    [INTEGER_VALUE] [numeric](18, 0) NULL,
  • 5
    The general guideline is to allow for 2.5X current size to support an online rebuild. Your base table is ~256 GB; 2.5 x 256 GB = ~ 640 GB. That all has to be logged so that it can be rolled back. – Aaron Bertrand May 8 '15 at 16:11
  • 2
    Instead of doing rebuild at one go you have to do it piecemeal. Make set of indexes and rebuild them and take transaction log backup to truncate logs. Or you can change recovery model to BULK LOGGED it will do minimal logging but you would loose PIT recovery for that period. – Shanky May 8 '15 at 16:23
  • Have you considered partitioning in the future so you can index 1 partition at a time, you could get some great benefits with partition elimination as well. – Ali Razeghi May 8 '15 at 19:17

Regardless of your recovery model whenever an operation (index rebuild or reorganize) modifies a data page that page modification is put into the log. In your case changing the fill factor can add more data pages, and move data to new pages. SQL Server has to make room for the new data and to do that it has to keep track of the page changes. The more pages that are changed, the more entries will be put into the transaction log.

In your case I would ask, what storage are you using, and what is your fragmentation like? If you're using flash storage, I'd tell you that rebuilding indexes becomes much less important than it does if you're using magnetic storage.

| improve this answer | |

Why is the LOG file growing so much?

The answer is within the script (mentioned in question) that been executed:

Online = On

As it already confirmed in the comments, while you rebuilding index online there would be additional space (probably double of index size) required in LOG File as every index's (clustered or non-clustered) online operation generates more redo than a normal OFFLINE rebuild, here is the case study you may want to look at for further more details

Fillfactor = 85

The fill-factor is consideration to reduce potential page splits, it basically providing enough space in page for index expansion as data is added to the underlying table, which means in your case when you tried to rebuild a index with fill-factor 85% (wherein by default is 100%), it tried to relocate 15% of pages into new page which cause more storage requirement, Fill-factor might not be requirement on every table, the decision has to be made considering read and write overhead operation on particular table/index. Further more details on fill-factor

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.