Over the last month, I published several blog posts about backing up the ConfigMgr database, backing up ConfigMgr reports, and the importance of re-indexing the ConfigMgr database. Each of these how-to posts is setup as a single task, but wouldn’t it be nice if you could combine all three into one scheduled task? I know it would be great to do that, so this blog post will show you how to combine all three items into one SQL Server Agent Job.
To ensure that we’re all starting from the same point, make sure that you have read and implemented the steps in the following posts before starting:
First, review and record the existing Job names in a table similar to the one I have below.
Each of my existing jobs will be executed by using the following SQL queries:
Next, we need to create a New Job.
Provide a job title, and then click on the Steps node.
Give your Step a name and paste the first SQL query listed within the table created earlier in this post. Repeat this step for each Job within the table.
Once completed, your job step list will look similar to the following:
The only issue to be concerned with at this point is that all three jobs will start within a few seconds of each other and this is NOT ideal. The sp_start_job procedure will start the job and return immediately without waiting for each job to complete, so we need to fix this problem.
The trick to fixing this problem is to use the SQL command:
WAITFOR DELAY ’00:10:00′
This command will delay the next job for 10 minutes before continuing. I recommend adjusting the wait command for your environment (in my example below, I used 5 minutes).
To add a wait delay between jobs, select Job Step 2 and then click on the Insert… button. Give the new step a name and paste the wait command. Click OK to return back to the job step list. Repeat this process in-between each of the other steps.
Tip: Ensure that all step names are unique.
When you are done, your Job will look similar to the one below.
Once the job is saved, I recommend testing it and then scheduling it. I also recommend adjusting the scheduled time to whatever makes sense for you, but you should make it daily and do not have this task occur on the quarter hour increment.
For details about how to configure the schedule, please review the How to Automatically Backup ConfigMgr Reports using SQL Server Agent blog post. If you have any questions, please feel free to contact me at @GarthMJ.