PRECAUTIONS TO BE TAKEN FOR DATABASE BEFORE REBUILDING INDEX

  • hi,

    before rebuilding index what precautions to be taken

  • In most cases: none. A reindex won't damage or loose any data if certain steps are not done first. It just works.

    That said, there is a penalty to the system during the reindex process. An offline reindex will harshly block access during the rebuild, in exchange for the rebuild being quicker. Smaller tables may not be any problem at all, while larger tables could be noticable to users. Therefore, this is designed to be done during maintenance hours when users are not in the system.

    If your version of SQL Server supports Online reindexing, then SQL Server will not be so harsh during the reindex, in exchange for the process taking a lot longer. This is needed by 24x7 shops that do not have a maintenance window where the system can be taken offline at regular times.

    There are other impacts that a reindex has on a system, but the above are the main ones. Search this site for forum posts and articles that talk in more detail about this. For example, when to defrag indexes and how good of a job does defragging do, are additional topics related to what you are asking.

    Jim Murphy
    http://www.sqlwatchmen.com
    @SQLMurph

  • Make sure you have enough log space. Other than that, pretty much nothing

    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
  • Next to checking if you have enough log space, also check if your log file is in autogrowth, if it is then check if the disk where the log file is located has enough space.

  • I would change the database recovery to simple or bulk logged.

    Check if i have lot of space on the drives.

    Thank you.
    Aatish Patel

  • aatishpatel (6/27/2011)


    I would change the database recovery to simple or bulk logged.

    Bulk-logged unless you want to break the log chain and leave the DB open to major data loss risks until a backup (full or diff) can be taken.

    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
  • aatishpatel (6/27/2011)


    I would change the database recovery to simple or bulk logged.

    I would not do that unless you have a good reason and understand the tradeoffs; it's not going to hurt (other than using significant log space) any more than any other major operation.

    Most of my largest tables are in Full recovery mode databases; they get reindexed just like everything else.

  • aatishpatel (6/27/2011)


    I would change the database recovery to simple or bulk logged.

    [font="Arial Black"]WHY???[/font]

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • consider Backup also

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • Syed Jahanzaib Bin hassan (6/28/2011)


    consider Backup also

    Before rebuilding an index?

    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
  • looping myself

    M&M

  • mohammed moinudheen (6/28/2011)


    looping myself

    ??

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • Syed Jahanzaib Bin hassan (6/28/2011)


    consider Backup also

    What do you mean by considering the backups?

  • Yes

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • mohammed moinudheen (6/28/2011)


    looping myself

    ?

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

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