Index maintenance increase log size

  • In SQL Server 2008R2, we are running an Index maintenance job on Sunday.It rebuilds/reorganize the indexes ONLINE on a database in a loop. The max size in Indexes is 16GB , but my log size grows upto 160 GB.There are around 200 indexes. I understand that ONLINE rebuild causes log size to grow but I also think that once the rebuild of one index is complete, the log space should be available for reuse but that doesn't seem to be happening. Can it be a reason that since rebuild happens in a loop and loop runs fast , so essentially there are several rebuilds happening in parallel? Will putting a waitperiod of ,say 10 sec, after each rebuild help?

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • Is your database in Full recovery mode, and if so, how often do you back up the transaction log?

    John

  • yes, it's in full recovery and log is backed every 20 minutes. I checked this and log backups are happening fine every 20 min

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • Once the rebuild of any index has used up space in your transaction log, that space won't be freed up again until the transaction log is backed up. You have several options:

    (1) Allow more space for your transaction log

    (2) Back up your log more frequently during index maintenance

    (3) Use an index maintenance script that only rebuilds or reorganises indexes that are fragmented.

    John

  • John Mitchell-245523 (4/29/2013)


    Once the rebuild of any index has used up space in your transaction log, that space won't be freed up again until the transaction log is backed up. You have several options:

    (1) Allow more space for your transaction log

    (2) Back up your log more frequently during index maintenance

    (3) Use an index maintenance script that only rebuilds or reorganises indexes that are fragmented.

    John

    Hi John,

    Consider a scenario where in..

    u have to Create a DB & then backup the Logs every day... then what would you suggest for the mdf & ldf files to be.. in terms of Size..

    Like while creating the DB.. in properties u have an option to change & set the size..

    1) Initial Size

    2) AutoGrowth..

    Please suggesta best method of setting this for smooth execution...

    ************************************
    Every Dog has a Tail !!!!! :-D

  • Hi

    I don't have the option 1.

    Option 3 , I am already doing.

    So I am left with option 2 only. Here also, the log size takes 2 hours to grow to 160 GB. So when log backups are happening every 20 min, then it shouldn't grow to that size, right?

    John Mitchell-245523 (4/29/2013)


    Once the rebuild of any index has used up space in your transaction log, that space won't be freed up again until the transaction log is backed up. You have several options:

    (1) Allow more space for your transaction log

    (2) Back up your log more frequently during index maintenance

    (3) Use an index maintenance script that only rebuilds or reorganises indexes that are fragmented.

    John

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • Maybe you've got an uncommitted transaction or something like that. How much free space is there in the log file after the index maintenance has finished and the log has been backed up?

    John

  • If t-log growing to 160GB over 2 hours and you are running t-log backups every 20 minutes, sort of sounds like the index maintenance process may be running as a single transaction. This is only a guess since we can't see the routines you are running .

  • S_Kumar_S (4/29/2013)


    So I am left with option 2 only. Here also, the log size takes 2 hours to grow to 160 GB. So when log backups are happening every 20 min, then it shouldn't grow to that size, right?

    If you're rebuilding a 100+GB index during those two hours then yes, it should grow to that size. For index maintenance you need space in the log > total size of the largest index you're rebuilding.

    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
  • I don't think there are any long running transaction that time when maintenance is happening.Once the maintenance is over after 2 hrs, and if I run DBCC SQLPERF(Logspace), the used space is less than 1%. That means almost all space is free. Although I never checked the space after each transaction log backup.

    John Mitchell-245523 (4/29/2013)


    Maybe you've got an uncommitted transaction or something like that. How much free space is there in the log file after the index maintenance has finished and the log has been backed up?

    John

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • Okay, I will say it explicitly instead of implying it. Look at the code you use to run the index maintenance, is it running the rebuilds/reorgs as a single transaction for all the tables/indexes or is each one in a separate transaction?

  • Hi Gail

    My biggest index is 16 GB and size of all indexes combined is 60 GB. May be combined with other load, it reaches to 160GB. I was curious if I put a WaitForDelay after each rebuild, so that not many rebuilds happen in parallel, will that help?

    GilaMonster (4/29/2013)


    S_Kumar_S (4/29/2013)


    So I am left with option 2 only. Here also, the log size takes 2 hours to grow to 160 GB. So when log backups are happening every 20 min, then it shouldn't grow to that size, right?

    If you're rebuilding a 100+GB index during those two hours then yes, it should grow to that size. For index maintenance you need space in the log > total size of the largest index you're rebuilding.

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • Hi

    Each one is a separate transaction as I don't use any BEGIN TRAN etc. it's a simple while loop with rebuild statement between BEGIN and END.

    Lynn Pettis (4/29/2013)


    Okay, I will say it explicitly instead of implying it. Look at the code you use to run the index maintenance, is it running the rebuilds/reorgs as a single transaction for all the tables/indexes or is each one in a separate transaction?

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

  • If it's a simple while loop, they'll execute serially anyway and therefore putting a delay between them won't have much effect. Are you sure you're using option 3 that I suggested earlier? Something as sophisticated as that involves more than just a loop. Perhaps you could post your script, please?

    Also, are you seeing the whole 160GB used every time you rebuild your indexes, or did it just grow to that size once? How are you monitoring the log space used during index maintenance?

    John

  • The size grows every time during maintenance. I use DBCC SQLPERF(Logspace) and sp_helpdb to monitor it.

    Here is the edited while loop part:

    WHILE EXISTS (SELECT * FROM #tmpIndexes WHERE Completed = 0)

    BEGIN

    SET @CurrentRequireRebuild=

    CASE

    WHEN EXISTS(SELECT Object_ID FROM sys.dm_db_index_physical_stats(db_id(@Database), @CurrentObjectID, @CurrentIndexID, NULL, 'DETAILED') WHERE avg_fragmentation_in_percent>30) THEN 1

    ELSE 0

    END

    SET @CurrentCommand01=''

    IF (@CurrentRequireRebuild=0) OR (@CurrentIsLOB = 1 AND @CurrentRequireRebuild=0)

    BEGIN

    SET @CurrentCommand01 = ('ALTER INDEX ' + QUOTENAME(@CurrentIndexName) + ' ON ' + QUOTENAME(@Database) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' REORGANIZE ')

    END

    Else IF @CurrentIsLOB = 0 AND @CurrentRequireRebuild=1

    Begin

    SET @CurrentCommand01 = ('ALTER INDEX ' + QUOTENAME(@CurrentIndexName) + ' ON ' + QUOTENAME(@Database) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' REBUILD WITH (PAD_INDEX = ON, SORT_IN_TEMPDB = ON, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95)')

    End

    EXEC (@CurrentCommand01)

    John Mitchell-245523 (4/29/2013)


    If it's a simple while loop, they'll execute serially anyway and therefore putting a delay between them won't have much effect. Are you sure you're using option 3 that I suggested earlier? Something as sophisticated as that involves more than just a loop. Perhaps you could post your script, please?

    Also, are you seeing the whole 160GB used every time you rebuild your indexes, or did it just grow to that size once? How are you monitoring the log space used during index maintenance?

    John

    Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Viewing 15 posts - 1 through 15 (of 24 total)

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