SQL Maintenance plan moving to next task before task is complete

Question

Post reply

SQL Maintenance plan moving to next task before task is complete

lawson2305

SSCommitted

Points: 1624
More actions
June 9, 2016 at 1:23 pm

#400257

I'm having an issue where in a few instances my task are executing but it seems they are not always waiting for success or completion before moving on to the next task.
2008 server:
Example I have a Plan that runs a some tsql to set a db recover model bulked Logged and then after that it executes a sql agent job task which performs a large purge of data. We ran this last week and the maintenance plan moved on to the next task after the job purge that set the db back to full recovery model causing the db log to fill up.
I have a similar situation on a 2005 server where I have a maintenance plan sets bulked logged I then perform a tsql statement task to execute a reindex and I have noticed it has in the past also moved on and set the db back to full recovery model before completing the reindex.
How can I keep maintenance plans from moving to the next step until the task is fully complete?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27044 More actions · Answer 1

After the job sets the recovery model, does it call sp_start_job?

If so, that's your problem.

If a job step starts another job using sp_start_job, it will immediately move on to the next step. It does not wait till the job is finished.

I suggest that you create a job with a series of steps that run in order. Each step runs the actual code. The subsequent steps will not start until the previous step has succeeded or failed.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

lawson2305 SSCommitted Points: 1624 More actions · Answer 2

For my 2008 server maintenance plan yes it looks like that is what it does:

EXEC msdb.dbo.sp_start_job @job_id=N'e24ac97f-381c-4e85-b79d-fbfc8ce30e58'

For my 2005

EXECUTE [dbo].[IndexOptimize] @databases = 'USER_DATABASES',

@LogToTable = 'Y',

@UpdateStatistics = 'ALL',

@OnlyModifiedStatistics = 'Y',

@StatisticsSample = 100;

lawson2305 SSCommitted Points: 1624 More actions · Answer 3

So any recommendations on how I can use a plan to make sure the plan does not move forward until the task is complete.

I will move forward on getting the sql job turned into a t-sql execution so that I avoid the issue I'm having with the 2008 plan. My concern is this is not a 100% fix since I have had issues with the 2005 server maintenance plan.

I need to do more digging on the 2005 issue as it has not happened since April but has happened and I can't figure out why. I think though I may need to take that one on a more case by case basis and do some serious detective work on it when it happens.

Michael L John One Orange Chip Points: 27044 More actions · Answer 4

Are you stuck with the maintenance plans?

"IndexOptimize" is a procedure from Ola Hallengren. https://ola.hallengren.com/

It appears that you, or someone, has implemented this or part of it.

The main problems with using the maintenance plan wizards is one of control. They tend to be overkill, are prone to unexplained failures, and if you make minor changes they break.

If you look at Ola's solution, and that is not the only one out there, it may make sense to use that, and modify the various jobs and timing to fit your needs.

As an example, we have 8 standard jobs in place for maintenance.

3 for full, diff and log backups

1 for DBCC CHECKDB

1 for reindexing

1 for reading the error log

1 for cleanup of the log files, job history, and cycling the logs.

The full and diff backup jobs consist of three steps, the first step runs the same code that is contained in the CHECKDB job as the first step. Once this step completes, it then performs the backup. The last step starts the reindex job.

The only reason we have created a separate job for CHECKDB is to be able to run this as needed. It's enabled, but it is not scheduled.

You could do something similar, the first step would be to run the code that sets the recovery, then the next step runs the import, and so forth.

From what you posted, your simple solution would be to replace the sp_start_job wiht the code contained in that job.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

lawson2305 SSCommitted Points: 1624 More actions · Answer 5

So creating steps in the sql job and scheduling the job is basically the same as the maintenance plan. If SQL jobs are just the better route to go then I guess I can consider using this method instead.

Thanks for the information regarding all of this.