Post reply

Ola Hallengren Index Optimize - Log Growth

  • September 16, 2014 at 12:49 pm

    #99065

    Hi All,

    I'm implementing Ola's maintenance solution on a 2008 R2 (SP2) instance. When running the IndexOptimize step on a 52 GB database, the transaction log grows by approximately 12GB. The database is in full recovery mode with one daily full backup followed by hourly log backups.

    The sqlcmd -Q being used is:

    EXECUTE dbo.IndexOptimize

    @databases = 'MY_DB',

    @FragmentationLow = NULL,

    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

    @FragmentationLevel1 = 5,

    @FragmentationLevel2 = 30,

    @UpdateStatistics = 'ALL',

    @OnlyModifiedStatistics = 'Y',

    @LogToTable = 'Y'"

    Has anyone else witnessed similar growth when using the IndexOptimize feature? I'd like to minimize log growth as much as possible. Is it possible to minimize logging by changing the args?

    All help appreciated. Thanks in advance.

  • September 16, 2014 at 1:02 pm

    #1745887

    That's about right, maybe on the low side. In full recovery index rebuilds are size-of-data operations, so log usage equal or greater than the size of the index.

    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 2 posts - 1 through 1 (of 1 total)

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