I'm moving a database to SQL Azure and with that I lose the maintenance plans so I am going to use a Worker Role to schedule them. When I open the properties of the Rebuild Index Task and click on View T-SQL I noticed that it generates the T-SQL on the fly for all the indexes in the Database. If I copy the generated T-SQL I will have to maintain it every time I add a new Index.

Is there a way to do the same that the Task is doing by using T-SQL? Iterating over all indexes and creating the T-SQL for Rebuilding the index on the fly?


I recommend either of the following:

Either of these can accomplish the task of maintaining indexes without requiring you to rebuild the scripts when indexes are added or removed.

One of the biggest problems I've seen with using the maintenance plan is the fact that it appears to iterate through every single index and place the index rebuild into a single script. Then the script is compiled and finally run.

Now imagine that you have a 150 databases each with over 1000 indexes. This becomes a maintenance plan that takes 8 hours to run.

  • Will take a look at these! – Jonas Stawski Jul 11 '12 at 21:47
  • Ola Hallengren's solution does not support Sql Azure. He is looking into it, however. – Dusda Oct 31 '12 at 19:59

To answer my own question.

Since I'm doing this on a Worker Role, i'm first querying for all the indices using the T-SQL from this blog, storing the information on a list and creating the SQL with a StringBuilder and a for each loop.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.