Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

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.

share|improve this answer
    
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
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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