Increase in backup Log size during rebuild index.

  • Hi,

    There is drastic increase in log size during rebuilding indexes even in Bulk recovery mode.

    Any way to reduce the log size???

    Regards,
    Saravanan

  • If you mean the size of the log backups, then the answer is no.

    The BULK_LOGGED recovery model allows the index rebuild to complete faster, since it will be minimally logged, but the next log backup after the operation completes will include all the data pages from the rebuild, for recovery purposes. There's really no way around this, if you want to maintain a restore path.

    If that does not answer your question, please give more details of the problem you are seeing.

    Paul

  • Hi Paul,

    I have configured log shipping. My bandwidth is just 2 Mbps.Yesterday i rebuild and reorganized the indexes which made log backup to increase gradually which in turn increased the network traffic .All my copy jobs still executing . We have around 6500 indexes. How to run maintenance plan without effecting log shipping.

    Regards,
    Saravanan

  • General recommendations:

    1. Get a faster link 😉

    2. Only rebuild or reorganize indexes that absolutely require it

    3. Spread the bare-minimum of rebuilds and reorganizations out over time

    4. Compress the log backups before shipping them across the link

    5. Consider partitioning large indexes (Enterprise only) and rebuilding at partition level

    Essentially, with such a slow link, you need to have a smarter maintenance script - an SSMS/SSIS maintenance plan is not suitable. Compressing log backups will require you to handle log-shipping manually, since the built-in support is quite basic.

  • Paul,

    We are using standard editions. I cant make use of 4th and 5th point since it is not supported .Already i have increased the bandwidth from 1Mbps to 2 Mbps.Im rebuilding and reorganizing only the indexes which it required.

    Still there is a increase in log. Any other options ????

    Regards,
    Saravanan

  • Saravanan T (4/5/2010)


    I'm rebuilding and reorganizing only the indexes which it required.

    Are you sure? If you are using a Maintenance Plan, the answer is almost certainly 'no'.

    Remember that fragmentation only affects performance when performing an ordered scan. Unless you have performed a detailed analysis to identify structures that are regularly scanned in logical order, and confirmed that fragmentation causes measurably poor performance...you are probably rebuilding indexes unnecessarily.

    My suggestion was to identify indexes that absolutely require maintenance. Part of that assessment requires that you can show a concrete benefit. Given the bandwidth restriction, you might have to accept that you can only rebuild a few heavily-scanned indexes, and that you will have to write a script to spread these operations out over time.

    Compressing the log does not require Enterprise Edition. You cannot do that in any edition of SQL Server 2005 (backup compression is available in SQL Server 2008 EE).

    My suggestion is that you set up manual log shipping, and use a utility compression program on the log backups before copying them across the link.

  • Thks for your time . Will Update statistics also increase the log size?

    Regards,
    Saravanan

  • Saravanan T (4/5/2010)


    Thks for your time . Will Update statistics also increase the log size?

    Update statistics does not perform any logged operations, so no.

  • Saravanan T (4/5/2010)


    Im rebuilding and reorganizing only the indexes which it required.

    something i have seen before, ensure you dont have a maint plan with a rebuild and a reorganise task defined

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Im not using Maintenace plan. Im using a SP which reorganize or rebuild the indexes depending on the fragmentation level.

    Regards,
    Saravanan

  • Saravanan T (4/6/2010)


    Im not using Maintenace plan. Im using a SP which reorganize or rebuild the indexes depending on the fragmentation level.

    Good. Consider modifying your script to only process indexes where you know that fragmentation definitely impacts on production performance (ordered scans). Take a look at the sys.dm_db_index_operational_stats dynamic management view.

  • Im taking fragmentaion percentage in this DMV sys.dm_db_index_physical_stats.

    wat column i shd make use in sys.dm_db_index_operational_stats.Any article on sys.dm_db_index_operational_stats???

    Regards,
    Saravanan

  • Also look into lowering the fill factors of your indexes so that they do not get fragmented as often.

    See discussion in http://www.sqlservercentral.com/Forums/Topic892938-1550-2.aspx#bm898207.

    I use this query to find the usage statistics of indexes (user_scans column will tell you if an index needs to be defragmented):

    SELECT

    OBJECT_NAME( s.[object_id] ) AS TableName

    ,i.name AS IndexName

    ,s.user_updates

    ,s.user_seeks

    ,s.user_scans

    ,s.user_lookups

    FROM

    sys.dm_db_index_usage_stats s

    INNER JOIN

    sys.indexes i

    ON

    s.object_id = i.object_id

    AND i.index_id = s.index_id

    WHERE

    OBJECT_NAME( s.[object_id] ) = '<table_name_goes_here>'

    AND db_name(s.database_id) = db_name(db_id())

    AND objectproperty(i.object_id, 'IsIndexable') = 1

    ORDER BY

    OBJECT_NAME( s.[object_id] )

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 14 posts - 1 through 13 (of 13 total)

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