SQL Server Performance

Transaction log file growth during index de-fragmentation

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Panik77, Mar 8, 2009.

  1. Panik77 New Member

    The transaction log file for our production database is growing during the weekly job we run to de-fragment the indexes.
    The job executes the following steps:
    • Transaction log backup
    • Change database recovery model to "Bulk Logged"
    • De-fragment indexes
    • Change database recovery model to "Full"
    • Transaction log backup
    The step to de-fragment the indexes is based on the script here: http://technet.microsoft.com/en-us/library/ms188917.aspx under the section: "D. Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes"

    So the indexes are either re-organised or rebuilt based on the value of "avg_fragmentation_in_percent" from "sys.dm_db_index_physical_stats"

    The database data file is: 2048 MB in size, and the Transaction log file is also 2048 MB in size, before the index de-fragmentation.
    When the de-fragmentation ran, the log file grew by 512 MB to 2560 MB.
    I thought that changing the recovery model, so that index rebuild and reorganisation operations are minimally logged, would stop the transaction log file growth.
    How can I limit the transaction log growth during the index de-fragmentation step?
    How can I calculate what size the Transaction log file should be, so that it doesn't grow by itself?
  2. moh_hassan20 New Member

    How can I limit the transaction log growth during the index de-fragmentation step?
    you can direct all logs of index rebuilding in tempdb by using
    with SORT_IN_TEMPDB = ON (default is OFF ) in sql 2005 or higher
    How can I calculate what size the Transaction log file should be, so that it doesn't grow by itself?
    - compute the size of index using sp_spaceused
    - size needed for log =~ 1.5 index size
    if total index size is 500 MB
    expected log size = 1.5 *500 = 750MB , that size will be freed after indexing and reused
    so plan for tempdb database for that extra size needed
  3. johnson_ef Member

    Though you have changed the recovery model to Bulk-logged, the changes will captured in log. But it will not retain in the log file. Each transaction in the SQL Server will be cpatured in Log even its in Simple or Bulk-Logged or Full recovery model, but except Full recovery model the logs will be cleaned up after the check-point. Due to this, the log space will be given back to system. But in Full recovery, the clean-up will be happened either Log backup or explicit activity like truncate log.
    I hope you have got the point.
  4. Panik77 New Member

    Hi Guys
    Sorry that I have taken so long to respond, but I have been investigating my problem a little more, trying to get my head around what is actually happening.
    I believe that the indexes are currently small enough to be sorted in Memory, so using the "SORT_IN_TEMPDB = ON" option won't have any effect.
    Also, my understanding is that if temporary disk space is required for sorting, then the data file is used, not the Transaction log file. (please correct me if I'm wrong).
    If I understand this process correctly what is happening is that after each index has been rebuilt the new index is written to the data file, and the space for the old index is de-allocated. But the process of writing the new index fills up space in the transaction log equal to the size of the index.
    If the index is reorganised then the transaction log is filled with the transactions required to sort the index.
    Is this correct?
  5. Panik77 New Member

    I have changed the Index de-fragmentation process, so that indexes are alway rebuilt and never reorganised.
    I also shrank the Transaction log file down to 1 GB.
    Over the weekend the indexes were de-fragmented successfully, and the Transaction log did not grow.
    Problem solved. [:)]
    I did not use the "SORT_IN_TEMPDB = ON" option, but I think that I will enable this option later, so that a single large table does not cause the data file to grow while rebuilding the indexes.
    Thank you for your help.

Share This Page