Post reply

space used in transaction log during rebuild index

  • September 30, 2011 at 5:50 am

    #249002

    Hi,

    I would like to know if there is a solution to estimate the size of a user database transaction log during the rebuild of an index (recovery model = full).

    I would like to do something like that :

    ALTER INDEX [IndexName] ON databasename.dbo.tablename REBUILD WITH (FILLFACTOR=80, SORT_IN_TEMPDB=ON, ONLINE=ON)

    for example, I have an index on a table that contain 30000000 rows, avg_fragmentation_in_percent ~ 80, page_count ~ 1240000

    Thx

  • September 30, 2011 at 7:31 am

    #1388847

    At least the total size of all the indexes rebuilt between two log backups. Probably more.

    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

  • September 30, 2011 at 9:29 am

    #1388908

    I want to know for one index, knowing the page_count, the avg_fragmentation, the number of rows ...

  • September 30, 2011 at 9:34 am

    #1388912

    At least the size of the index (in kB or MB), probably more because of the overhead of log header, log block header, etc.

    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