none
sql DB maintenance order RRS feed

  • Question

  • which order to follow db maintenance


    1.index optimization.

    2. integrity check.

    3.finally full backup.


    OR

     

    1. integrity check.

    2.index optimization.

    3.finally full backup.

     
    Saturday, May 23, 2020 4:25 AM

All replies

  • Why do you doubt on the order matters? The result will be the same.

    On 1) if the database is in corrupt state the index reorg will fail and same on 2) with first integrity check.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, May 23, 2020 4:36 AM
    Moderator
  • Seems like you have a big maintenance window. What you are suggesting is old technique usually used for low complex DB"s. All the 3 tasks combined can take time and can put lot of load on the system.

    Put integrity checks on Sunday and Index optimize and full backup on Saturday. With index optimize and full backup having 10-12 hours difference.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Saturday, May 23, 2020 7:41 AM
    Moderator
  • I would say integrity check before index rebuild/reorg. The reason for this is that if there is a corruption in an index, this may be removed by the index rebuild. But you want to know that there has been corruption, because even if it was a repairable corruption, this is a strong warning sign that something is not working as it should. If you cannot connect the corruption to a known event like a power failure, you should assume that the hardware is rotten and replace it.

    When you take the full backup matters less. You should always keep full and log backups for at least month back, so that in case of corruption you can restore a clean backup and restore logs.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, May 23, 2020 8:22 AM
  • I would say integrity check before index rebuild/reorg. The reason for this is that if there is a corruption in an index, this may be removed by the index rebuild.

    Just to make your comment more meaningful, this only applies to non clustered indexes (NCI) but there are chances this may not even work for NCI sometimes. AFAIK a clustered index corruption cannot be resolved by dropping and creating it, reason, CI is basically the physical table itself. 

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Saturday, May 23, 2020 12:26 PM
    Moderator
  • Just to make your comment more meaningful, this only applies to non clustered indexes (NCI) but there are chances this may not even work for NCI sometimes. AFAIK a clustered index corruption cannot be resolved by dropping and creating it, reason, CI is basically the physical table itself. 

    You are very right in that regard, and I should have made this clear in my post. When it comes to a clustered index, the corruption could be on the non-leaf pages, and in that case, the corruption may be possible to repair with a rebuild. But the non-leaf pages is a smaller part of the index, so those chances are very slim.

    Thank you very much for pointing this out!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, May 23, 2020 2:19 PM
  • Hi KIRUBAKARAN J A,

    If must choose one from these two options,choose as next:

    1.Integrity check;

    2.index optimization,

    3.finally full backup.

    And this maybe helpful: sql-server-maintenance-plan-best-practices-on-tasks-and-schedulingSQL Server Maintenance Solution

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by AV111 12 hours 11 minutes ago
    17 hours 3 minutes ago