SQL Server Performance

Subplans scheduling very strange behaviour

Discussion in 'ALL SQL SERVER QUESTIONS' started by Wentu, Sep 14, 2012.

  1. Wentu New Member


    I have a problem with scheduling a Maintenance Plan (MP) with 3 subplans (SP).
    SP1 is scheduled to run hourly,
    SP2 daily at 7.00 and
    SP3 on sundays at 8.00

    Reading MP history I see that what happened (I know it seems crazy) is:
    11: SP1 runs and executes all the tasks of SP1 SP2 and SP3
    12: SP2 runs and does the same
    13: SP3 runs and does the same
    14: SP1 runs and does the same

    From the job Activity monitor, SP1 has last run time at 14, SP2 and SP3 are *never* been executed.
    All of the SP are scheduled correctly in the Job Activity Monitor (SP2 for tomorrow at 7, SP3 for next sunday at 8)

    Do you have any idea what is happening?

    Thankx a lot

  2. Shehap MVP, MCTS, MCITP SQL Server

    First, welcome to Forums

    It sounds like there are sub call of SP2, SP3 under SP1 that make the schedule of SP1 is enough to run the 3 SPs hourly …and on the other side of 2 other jobs , it seems their start date hasn't come yet

    Kindly work out this assumption and let me know you feedback
  3. Wentu New Member

    Thank You Shehap for your answer.
    The only task SP1 is executing is a Transaction Log BackUp. I don't even know how I could tell to SP1 to invoke SP2 and SP3 so I am pretty confident your suggestion is wrong BUT, is there a way I could have invoked SP2 and SP3 from SP1 without knowing it? maybe some hidden flag somewhere that I left checked without knowing its effect? Maybe if there is a "executeEverythingAfterMe" flag somewhere I could check it but so far nothing seems to make a connection among the SPs. All the tasks I put in the SPs are perfectly normal tasks you can find during a MP creation (backups, index maintenance, statistics maintenance, cleanings and so on) and I am not using SQL Query tasks so far.
    Thank You again
  4. Wentu New Member

    After a lot of tries I found the reason for this behaviour.
    I had set the property Disable of each SP to "False" instead of the default "True" and this causes the execution of all the SPs when just one is invoked.

Share This Page