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) ?
SQL Server 2012 R2 on Windows Server 2008 R2
Best Answer
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:
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.