Post reply

INDEXDEFRAG When Log shipping Implemented

  • August 23, 2011 at 3:08 pm

    #256127

    Hello Friends,

    I would like to know whether i can run DBCC INDEXDEFRAG on a database which have log shipping implemented?

    Does it impact logshipping ?

    Because i don't want run reindex. Currently mdf file size is 284 GB and 50 GB of Log file. and production server is running 24X7. I don't want to make server really slow and network too much busy.

    Is that the good option to defrag index one by one using INDEXDEFRAG without loosing Log shipping or we can not.

    I am waiting for your valuable suggestion.

    Thanks Team.

  • August 24, 2011 at 2:13 am

    #1372396

    You can do the defrag because it will not cause the log file to grow huge.

    Rajesh Kasturi

  • August 24, 2011 at 7:09 am

    #1372529

    Will it cause to stop Log shipping ? Do i need to reconfigure logshipping again after defragmentaion ?

  • August 24, 2011 at 8:39 am

    #1372621

    Should not stop log shipping, DEPENDING on how you have it setup to transfer the files to another location.

    I work with a 210 GB database where I had to manually do an online defrag (used alter index with reorganize though) and it gave about 2 GB in log increase for each 15 minutes that the tail log was 'snipped'. There was no previous maintenance though so there was some heavy fragmentation that needed to be done. Log shipping did not break, but could have if something tried to move files while the log file was being created.

  • August 24, 2011 at 8:40 am

    #1372624

    No this will not stop log-shipping at all.

    You can use sys.dm_dm_index_physical_status to rebuild specific index, only if avg_fragmentation_in_percent is more than 30.

    Put that in job and split the index rebuild (Top N) into different time range so that log will be shipped in different time range and would not effect your live servers.

    Good luck.

  • August 24, 2011 at 11:58 am

    #1372765

    Hi,

    Thanks for your valuable suggestion. I will run "DBCC INDEXDEFRAG " one by one for the index with fragmentation average greater than 30%.

    Hope it will work without affecting log shipping.

    Thanks 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

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