If you’ve been doing the exercises, you may have noticed that there is a lot of archival data, specifically in the realm of the history of the jobs. This can be both good and bad, depending on your point of view. It can be good because you want to know about the history of your jobs if they fail.
If your job always succeeds, then you don’t really need to know about it, except for your own edification.
If your job always fails, then you really need to know about it to fix it.
A good way to think about this cleanup task is that this will unclutter the history of your jobs so that you will only see the information for the time period you specify. You can define any arbitrary time slice that you would like, but usually it’s going to be something like three days. Only keep history for three days, in other words; otherwise, the history for a job will go on forever and not really give you anything.
The overall purpose of these exercises, if you remember, is to make your life easier. If I were your boss, you would be in deep trouble if you keep history, log everything, and you don’t have any kind of cleanup tasks, but then come to me and ask for more storage because all your hard drives are full. Your next day would be spent reading this very book with a dunce cap on your head!
An important note for this chapter is that the task you learn here is about cleaning up SQL Server Agent’s logs—and that’s it. These aren’t the .txt files generated by the maintenance plans; that is in Chapter 6. What this chapter’s task does is clean out the logs that SQL Server Agent keeps regarding the status of the jobs. For many jobs, this can quickly grow to be a huge problem that is hard to sift through. Keeping it truncated to three days gives you plenty of time to look at any issues that may pop up, considering that you are checking your emails for any issues.
Setting Up the Maintenance Plan
Okay, down to business. This task is very easy to create. Go ahead and right-click the Maintenance Plans folder again from inside the Management folder, and select Maintenance Plan Wizard. The generic interface you are presented with in Figure 5-1 should be something you are familiar with by now.
Figure 5-1. Select Plan Properties
Update it to appear as shown in Figure 5-2.
Figure 5-2. Select Plan Properties (updated)
That sets us up for the next section, which is …? That’s right, the scheduling of the task. Remember, it makes no sense to have a task that isn’t scheduled. That basically means that you intend to run the tasks manually, which completely defeats the purpose of using SQL Server Agent to automate these tasks for you.
Click the Change… button to set up the schedule. Initially, the schedule is incorrect, so let’s change it to the settings shown in Figure 5-3.
Figure 5-3. New Job Schedule
Occurs: Daily
Occurs once at: 12:00:00 AM
You should see a result like that shown in Figure 5-3 when finished.
Told you this was easy. Click OK to move on. Notice that you go back to the screen you set the options on before, except now the schedule block is filled in. Click Next to continue.
Choosing Tasks
Now we actually have to set what we’re going to do, because we didn’t yet. Figure 5-4 shows the tasks that we are going to choose from.
Figure 5-4. Select Maintenance Tasks
Obviously, we’re going to choose Clean Up History here and then click Next.
Figure 5-5 shows the next screen from which you choose the order for the tasks. Since we have just the one task, we can click Next here and move on. However, while we are here, you might want to look closer to see an interface error that, while humorous, can also cause you to lose work.
Figure 5-5. Select Maintenance Task Order
Are you seeing the screen in Figure 5-6 now? Click inside the area where the Clean Up History text appears. Notice that the Move Down… button becomes active.
Figure 5-6. Select Maintenance Task Order
Weird. Go ahead and click the Move Down… button now. Nothing bad could happen, right? Figure 5-7 shows that something bad did indeed happen.
Figure 5-7. Error!
D’oh! Microsoft owes me a thank-you for debugging their interface! I expect to see this fixed in future versions of SSMS.
When you see the Define History Cleanup Task screen, you are ready to continue.
Define What to Clean
The next piece of the puzzle is to define what we want to clean up and how often, as shown in Figure 5-8.
Figure 5-8. Define History Cleanup Task
Initially, Figure 5-8 shows what you see when you go to this screen. Just change the value to “3 days” and you’re good to go. Leave the three check boxes on because we want to cleanup those three areas. Your updated interface should appear as shown in Figure 5-9.
Figure 5-9. Define History Cleanup Task (updated)
Click Next when you’re ready to continue.
The next screen gives you reporting options. You can write a report, email the report, or both. I suggest both in case you don’t have access to either option. If you’re going to use the text file option, make sure that you choose a folder location that is set up specifically for storing log files. I typically tend to use the folder structure that I described in Chapter 1, and point all the maintenance logs to the Backup folder; this means that transaction logs are the only logs that go in the Logs folder.
If you’re going to choose to email the report, click the check box and select the operator that we set up back in the database mail discussion in Chapter 4.
Your interface should now resemble Figure 5-10, with your values in place of mine.
Figure 5-10. Select Report Options
Review
Click Next and get ready to finish this task. You will see the summary of what we’ve done so far in this task. Expand everything. You should see the information displayed in Figure 5-11.
Figure 5-11. Complete the Wizard
To review, just run over the actions listed in the Complete the Wizard screen and make sure that everything is how you need it to be. If it isn’t, just click the Back button and fix whatever you need to. When you’re ready to implement, click Finish. You should see what is shown in Figure 5-12.
Figure 5-12. Maintenance Plan Wizard Progress
Have I mentioned how much I love the green check boxes? They tell us that everything checked out okay and that the maintenance plan has been added to the maintenance plan subsystem to be executed by SQL Server Agent on the set schedule.
Remember to change the default name of the Job now! Double-click the job name in the Jobs folder in SQL Server Agent and change it to something memorable. If you recall, we named the maintenance plan Cleanup History, so I named the job Cleanup History as well. The full name is now Cleanup History, as shown in Figure 5-13.
Figure 5-13. Job Properties
Remember to set up the rest of the options in this Properties area as we did earlier. Your Jobs folder should now look like Figure 5-14.
Figure 5-14. SQL Server Agent Jobs
Your Maintenance Plans folder should now look like Figure 5-15.
Figure 5-15. Maintenance Plans
Summary
Five chapters and three maintenance plans down so far. Notice that we didn’t create a maintenance plan for the SQL Server Agent Job chapter. That is by design. We didn’t want a maintenance plan for it because I just wanted to show how to create the job independent of the maintenance plan. In other words, there is a definite separation between the job and the maintenance plan, which you should recognize by now.