4

I have a weekly and nightly SQL Server Agent jobs executing a weekly and nightly maintenance plans, resp.

These weekly (on Sunday) and nightly (on other 6 nights of the week) maintenance plans differ only in the 1st task from total consecutive six steps. That is, in nightly/daily maintenance plan the 1st step is "Reorganize Indexes on all User databases" and in weekly/Sunday maintenance plan the first step is "Rebuild Indexes on All User Databases".

Is it possible and how to run just one (instead of two) maintenance plan, all 7 nights of week, with condition that on 6 days the 1st step is one (Reorganize Indexes) and on the rest 1 day of week the 1st step is another (Rebuild Indexes) ?

Daily Maintenance plan

SQL Server 2012 R2 on Windows Server 2008 R2

Improve this question

1 Answer 1

Reset to default
5

I am not sure how dirty you want to get on this one but the only method is going to be opening up SQL Server Data Tools and building (or modifying) an SSIS package. One would be to import the maintenance plan into SQL Server 2012 Data Tools and modify the SSIS package. The other would be to create it from scratch. You have the same options as the maintenance plans when you create an SSIS package.

The one thing you want to add is an Expression Task, which is an awesome addition to SQL Server 2012. You could use the DATEPART SSIS expression to determine whether it was a weekday or weekend based on the system variable for package start time. Now I am not the best with expressions but this would be the one I would use:


DATEPART("dw",@[System::StartTime])

This will return a number between 1 and 7, with 1 = Sunday and 7 = Saturday. So from that task you would have two precedence constraints going to each path of your maintenance plan. If the value is 1 (one) then it goes to the rebuild index task, if it is anything else it goes to the reorganize task.

Although if I was going into this much detail I would stop using the index task with maintenance plans. I would suggest going to using T-SQL code that actually helps you maintenance the index fragmentation much more efficiently. I will say though I don't know if SQL Server has improved in 2012 with the index task. The most well known solution for index fragmentation management can be found here, Ola Hallengren.

Improve this answer
1
  • That expression could be bound to the Disabled property of the tasks (with appropriate changes, of course). Maintenance Plans support doing this; there's no need to use SSDT in this case. I strongly agree with the last paragraph, though.
    – Jon Seigel
    Oct 31, 2013 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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