Post reply

ALTER INDEX REBUILD use of Tx Logs

  • April 4, 2016 at 8:08 am

    #326246

    Hi

    We have a very big table with a clustered index @1Tb.

    Database is currently in simple recovery mode, and I can rebuild the CI in about 30 mins, and there is little impact on the Tx Log wrt to size, i.e. little growth. There is growth in the datafiles while the index is rebuilt, which is what I would expect while SQL makes a copy of the index.

    When I put the database into FULL recovery mode, the rebuild time extends to 120 mins (which is OK), but the Tx Log grows to 1Tb, which is not going to be OK!

    I would have expected the difference in the Tx log behavior between SIMPLE and FULL recovery mode to just be how the log space is freed after the rebuild completes. Is SQL storing all the data in the Tx log for rollforward/rollback activity?

    Can somebody explain this behaviour?

    Thanks in advance.

  • April 4, 2016 at 8:24 am

    #1869526

    Index rebuilds can be minimally logged in simple and bulk-logged recovery model. It's fully logged in full recovery, which means it's a size-of-data operation in terms of log space used.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 5, 2016 at 3:43 am

    #1869739

    Thanks for your reply, that certainly explains what is happening.

    It's a shame there is no "nolog" option for index rebuilds in FULL recovery mode. Having >1Tb tx log disk space lying idle for the occasional rebuild of this index seems such a waste.

  • April 5, 2016 at 5:15 am

    #1869764

    Green Armchair (4/5/2016)

    It's a shame there is no "nolog" option for index rebuilds in FULL recovery mode.

    There's no 'nolog' anywhere in SQL. All data modifications are logged. Some (like bulk insert, index rebuilds and others) can be minimally logged in simple and bulk-logged recovery models, but they're still logged.

    Probably because most people don't like the idea of having to restore the DB from backup if an operation fails.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply