2

I'm using Ola Hallengren's scripts to perform maintenance on my databases. In the Maintenance Plan Designer I am trying to setup a plan that works step by step, but no matter what I try everything gets executed at one time.

My first and second attempts, left column in the photo, was to use "execute job" and call each job in the order I want. The first constraint was "success" but all jobs execute at once. So I tried "completion" next. Same result. I then constructed the same tree using execute t-sql instead of execute job using this code:

GO
EXEC msdb.dbo.sp_start_job @job_name ='DatabaseIntegrityCheck - USER_DATABASES';
GO

One again I got the same result, everything executes at the same time. I even tried adding a @return_value for executing sp_start_job but that did nothing either. Finally I tried one script like this:

GO
EXEC msdb.dbo.sp_start_job @job_name ='DatabaseIntegrityCheck - USER_DATABASES';
GO
EXEC msdb.dbo.sp_start_job @job_name ='DatabaseIntegrityCheck - SYSTEM_DATABASES';
GO
EXEC msdb.dbo.sp_start_job @job_name ='IndexOptimize - USER_DATABASES';
GO
EXEC msdb.dbo.sp_start_job @job_name ='DatabaseBackup - USER_DATABASES - FULL';
GO
EXEC msdb.dbo.sp_start_job @job_name ='DatabaseBackup - SYSTEM_DATABASES - FULL';
GO
EXEC msdb.dbo.sp_start_job @job_name ='DatabaseBackup - USER_DATABASES - LOG';
GO

And it also executes everything at once. Besides scheduling them one by one in the maintenance plan editor at different times, is there any way I can make them execute one after another. I'm not concerned if they succeed or not, I just want one to run then the next instead of everything at once.

Maintenance Plan Screenshot

2

Why not just run each job as a seperate step?

enter image description here

And so on and so forth

  • I'll give this a try tonight and see how it works. Thanks. – J Hays Nov 18 '15 at 19:49
  • This seems to be the key, I guess when you set it up under the Maintenance Plan screen it only counts it as a success/completion when the code executes, not that the job completes. This worked much better. – J Hays Nov 23 '15 at 14:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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