locked
Reindexing RRS feed

  • Question

  • hi Guys,

    I'm working on re-indexing a table using some commands from ALTER INDEX REBUILD from Microsoft. The indexes will be tested for threshold fragmentation. my plan is once the reindex is executed, a transaction backup will occur while controlling the size of the log file.  The query impose time limitations or stop reindexing after the specified amount of time has elapsed.

    my question,

    1. How can I integrate a query which checks if transaction log is getting full and which runs a Tlog backup if over 70%

    2. How do I impose time limitation?

    Any reindexing script appreciated.

    thank you 

    Wednesday, May 20, 2015 9:21 PM

Answers

  • Ola Hallengren index scripts are quite popular

    https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    typically, if you do index maintenance regularly, you should not see signifacnt fragementation on continous basis that would eat up lot of transaction log. if you see this happend regulary - there is a problem in your database\application design or check you fill factor..

    I do not think stopping the index rebuilt in between process is job idea., it will have roll back the txn - which will again take up log space, you would be better to use that space and let the rebuild finish.

    however,you could stop rebuilding index after certain time limit.

    like - have the index rebuild job run at 3:00 am - it will continue to defragement the index in a loop and you can add a condition to check if the time is 5:00 am before the deframentation is done on next index , if so it will exit the script and you need to log this info somewhere,so that next the index script will continue from where it left off

    if it is not 5 am, it will pick up next index to be defragmented and would do rebuild index on it...

    in the same way, you can also check for free log space after each index rebuild and stop the process at 70%, if you desire..but generally you should have log file to unlimited growth and I believe, the log file auto growth kicks in at 25% free space, so, your calculations might be little off, if i am not wrong.

    if your log file is on resticted growth you could do that but then, that is not good way to do..

    dbcc sqlperf(logspace) should tell you the free space in the log


    Hope it Helps!!



    Wednesday, May 20, 2015 9:44 PM
  • Index rebuild is all or nothing operation if you stop it in between you loose the work done. Apart from what is suggested you can also have a Temp table which stores all your indexes which needs to be rebuild this temp table would have extra column as flag. When index is rebuild it would set flag as 1 now when the process starts again it would check the flags and only rebuild index wit flag Zero

    Your Idea about frequent transaction log backup is good.


    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 Article

    MVP

    Thursday, May 21, 2015 5:01 AM
    Answerer

All replies

  • Ola Hallengren index scripts are quite popular

    https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    typically, if you do index maintenance regularly, you should not see signifacnt fragementation on continous basis that would eat up lot of transaction log. if you see this happend regulary - there is a problem in your database\application design or check you fill factor..

    I do not think stopping the index rebuilt in between process is job idea., it will have roll back the txn - which will again take up log space, you would be better to use that space and let the rebuild finish.

    however,you could stop rebuilding index after certain time limit.

    like - have the index rebuild job run at 3:00 am - it will continue to defragement the index in a loop and you can add a condition to check if the time is 5:00 am before the deframentation is done on next index , if so it will exit the script and you need to log this info somewhere,so that next the index script will continue from where it left off

    if it is not 5 am, it will pick up next index to be defragmented and would do rebuild index on it...

    in the same way, you can also check for free log space after each index rebuild and stop the process at 70%, if you desire..but generally you should have log file to unlimited growth and I believe, the log file auto growth kicks in at 25% free space, so, your calculations might be little off, if i am not wrong.

    if your log file is on resticted growth you could do that but then, that is not good way to do..

    dbcc sqlperf(logspace) should tell you the free space in the log


    Hope it Helps!!



    Wednesday, May 20, 2015 9:44 PM
  • Index rebuild is all or nothing operation if you stop it in between you loose the work done. Apart from what is suggested you can also have a Temp table which stores all your indexes which needs to be rebuild this temp table would have extra column as flag. When index is rebuild it would set flag as 1 now when the process starts again it would check the flags and only rebuild index wit flag Zero

    Your Idea about frequent transaction log backup is good.


    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 Article

    MVP

    Thursday, May 21, 2015 5:01 AM
    Answerer