Very similar to the content of Chapter 5 is the topic of maintenance cleanup tasks . History and maintenance tasks probably could have been tied together, but I can understand why they are separate.
Separation of History from Maintenance
Why are cleanup tasks separate for history and maintenance? Let’s look at the differences between the two tasks. Figure 6-1 highlights some of the key differences to be aware of.
Figure 6-1. Differences between Maintenance and History Cleanup Tasks
What do these differences mean in layman’s terms? Here is what you need to know:
The Maintenance Cleanup Task cleans up the physical files left after executing a maintenance plan. When you view the file system where you are logging the maintenance plan steps, you will likely see quite a few files. These files are deleted when the Maintenance Cleanup Task is run (subject to the period of time specified).
The Maintenance Cleanup task cleans up the .bak and .trn files in the file system, but you need to have two separate maintenance plans for each file type, since we have our file system set up like I do (meaning that we are storing the .bak files physically separate from the .trn files).
The History Cleanup Task cleans up history in the tasks themselves, and not the physical files. When you right-click a job and select View History, that history goes away when the History Cleanup Task is run (subject to the period of time specified) because these rows are removed from the msdb database.
Now that we understand the difference between the two types of cleanup tasks, let’s look at setting up the Maintenance Cleanup Task.
Setting Up the Maintenance Plan
Remember that we need three separate tasks for this: one for the .bak files, one for the .trn files, and one for the .txt files. Why? Because SQL Server makes you choose a file extension to look for and delete if it is beyond a certain age, and database backups (.bak) and transaction log backups (.trn) are both physically and logically different.
We are going to name these plans Backups Cleanup, Logs Cleanup, and Text Files Cleanup. This should keep it nice and clean for us to easily see what goes where.
We are going to set up Backups Cleanup first, followed by Logs Cleanup, and finally Text Files Cleanup. Remember that if something goes wrong with your maintenance tasks, the .txt files referenced in the Text Files Cleanup task are a great place to start troubleshooting.
Backups Cleanup
The purpose of cleaning up backups is fairly simple: you don’t want to have multiple copies of a large database in a file system, keeping space from other applications or operating system functions that may need the space. For this reason, it is far easier to configure a default retention period and clean the backups automatically based on that retention period. The Backups Cleanup task allows us to create an automatic method to clean up these backups files.
Right-click the Maintenance Plans folder under Management and choose Maintenance Plan Wizard to get started. Enter Backups Cleanup as the Name, and something short and sweet in the Description box, and then click the Change… button to set the schedule. Change the Occurs drop-down to Daily but leave the rest alone, and then click OK. Your interface should resemble Figure 6-2.
Figure 6-2. Select Plan Properties
Good start. So, just like before, we’ve given it a good Name and Description, left Run As set to the default, and set the schedule to fire at midnight every night. Click Next and let’s set up the next part, as shown in Figure 6-3.
Figure 6-3. Select Maintenance Tasks
Obviously, you’re going to choose Maintenance Cleanup Task here and then click Next. You will then see the interface shown in Figure 6-4.
Figure 6-4. Select Maintenance Task Order
You can stop and generate the interface error, as we did in Chapter 4, but you need to start over if you do. Click Next when you’re ready.
The next screen is the meat and potatoes of this task. If you’re vegetarian, it’s the tofu and kale. Either way, the initial interface appears as shown in Figure 6-5.
Figure 6-5. Define Maintenance Cleanup Task
The first thing you notice is that there is a radio option at the top of the screen that gives you two options: “Backup files” and “Maintenance Plan text reports”. Does this imply that you can’t do both at the same time in the same task? In a word, yes. If you want to do both, you need to set two maintenance plans or jobs for each type of file that you want deleted. The reason for this is because in one area, you are deleting the actual backups of the database, and in the other, you are deleting the text reports for the maintenance plan operations. They’re different cleanup tasks, in other words, and behave differently. I would like to see Microsoft split these up into two separate tasks in future versions of SQL Server, but I won’t hold my breath.
For now, let’s concentrate on the “Backup files” option. The “Maintenance Plan text reports” options are next.
Deleting Backup Files
You have the option to delete the actual database backup files. Note that if you have the backup schedule in place as described in Chapter 1, then you are running a full backup, followed by a differential backup, and then by transaction log backups. With this in mind, if you were to implement this solution, you would only retain backups for a certain period of time. But the kicker is this: there is no difference, from a file system point of view, between full and differential backups. Read that part again. There is no difference, because they both have the .bak file extension by default. So, if you delete the full backup, but not the differential, you will not be able to restore the differential, because it depends on the full backup to restore. Without it, it cannot restore the data to the database and it is, in essence, completely useless.
Tip
Obviously, any time you delete data, it should be done very carefully. Please take the time to go through this thoroughly to alleviate any future headaches from accidentally deleting a backup that you may need.
Okay, so referring to Figure 6-5, make sure that “Backup files” is selected. The area under that, “File location”, determines if you want to delete a specific file or all the files in a folder with a certain extension. Although this sounds cut and dried, let’s look at the two options presented.
Deleting a Specific File
Let’s say that you have set up your logging to “Save all to the same file”. I can’t imagine why you would want to do this, but let’s say that you have a valid reason. This option finds and deletes a specified file in a specific location. Does that make sense? It will not traverse directories to find the file; it will only delete the file specified.
Searching and Deleting Based on Extension
Much like the backup options, this option lets you delete all the files in a specific location, including the first-level subfolders. Why the first-level subfolders? Because this option was presented when you set up the backup section way back in Chapter 1, and they want you to be able to properly manage your backups. It wouldn’t make much sense to let you write backups to a directory, and then not give you the power to manage the backups in that directory.
With those two nuggets of wisdom in mind, we are going to leave the default of “Search folder and delete files based on an extension” selected, and make sure to check the “Include first-level subfolders” as well. The default file extension should still be set to bak. The folder location needs to be set to where you defined the backups to be stored. This same location is where we are going to search for backup files and delete them based on the age of the backup. Figure 6-6 should be what you see at this point.
Figure 6-6. Define Maintenance Cleanup Task (completed)
The last bit is the “File age” part. You want to leave the “Delete files based on the age of the file at task run time” check box selected. The standard that I try to stick to is three days of file retention, so update the selections to match this or whatever your requirements are.
Be careful about this area. As I said before, this needs to be taken very seriously. You can’t undo a file deletion at this level. Be absolutely sure that you realize the implications of deleting these files. Once you make your selections, click Next to move on.
Once again, you are met with the option to either “Write a report to a text file” or to “E-mail report”. I recommend selecting both options; that way, you are aware of what is happening to your database as it happens. Make the changes you see in Figure 6-7, substituting your operator in the e-mail step.
Figure 6-7. Select Report Options
Click Next here and you will be shown the Summary page. Again, this is where you need to make absolutely sure that these settings are correct. Figure 6-8 shows what my Summary looks like.
Figure 6-8. Complete the Wizard
Click Finish when you’re ready. Hopefully, you see the green boxes shown in Figure 6-9.
Figure 6-9. Maintenance Plan Wizard Progress
I see all green check boxes. Very good!
Make sure that you update the job using the techniques from earlier chapters by double-clicking the job in the Jobs folder under SQL Server Agent. I changed mine to bak Files, but you can pick another name if you want. After you update it, your interface should look something like Figure 6-10.
Figure 6-10. SQL Server Agent Jobs
Next, we need to re-create the same job, but for .trn files (transaction log backups). Basically, this next part is going to be a cut-and-paste of the previous information, but updated for the .trn files.
Logs Cleanup
Much like before, the Logs Cleanup task is going to allow us to delete the .trn files present in the file system. Why do this? Aren’t the transaction log backups important? Yes, they are. But they are only relevant as long as they relate to a current backup set. Recall that backed up transaction logs can only be restored to a differential backup. Well, if the differential or the full backup file has been deleted, then there is no reason to keep the transaction logs for that backup. That’s what this task gives us: the flexibility to delete the transaction logs that just aren’t needed anymore.
Begin the same as before. Right-click the Maintenance Plans folder under Management and choose Maintenance Plan Wizard. Enter Logs Cleanup as the Name, and something short and sweet in the Description box, and then click the Change… button to set the schedule. Change the Occurs drop-down to Daily and leave the rest alone, and then click OK. Your interface should resemble Figure 6-11.
Figure 6-11. Select Plan Properties
Good start. So, just like before, we’ve provided a good Name and Description, left Run As set to the default, and set the schedule to fire at midnight every night. Click Next. Now let’s set up the next part, as shown in Figure 6-12.
Figure 6-12. Select Maintenance Tasks
Choose Maintenance Cleanup Task here and then click Next. You will then see the interface shown in Figure 6-13.
Figure 6-13. Select Maintenance Task Order
Click Next when you’re ready and you should see what is shown in Figure 6-14.
Figure 6-14. Define Maintenance Cleanup Task
Choose the “Backup files” option here. Choose your transaction log location and enter trn in the “File extension” field. Notice that we have selected our logs location this time, because this is where the transaction logs are being stored.
Figure 6-15 should be what you see at this point.
Figure 6-15. Define Maintenance Cleanup Task (completed)
Notice that we’ve selected 3 Days; the same as with the .bak option. The reason for this is that you won’t be able to restore a transaction log without the corresponding differential backup, so we’re going to delete all of the files by day so that we are always in sync. You want to leave the “Delete files based on the age of the file at task run time” check box selected. Click Next at this point.
Once again, you are met with the option to either “Write a report to a text file” or to “E-mail report”. I recommend selecting both options; that way, you are aware of what is happening to your database as it happens. Make the changes that you see in Figure 6-16, substituting your operator in the e-mail step.
Figure 6-16. Select Report Options
Click Next here. You will be shown the Summary page. Again, this is where you need to make absolutely sure that these settings are correct. Figure 6-17 shows what my Summary looks like.
Figure 6-17. Complete the Wizard
Click Finish when you’re ready. Figure 6-18 shows what you should expect to see at this point.
Figure 6-18. Maintenance Plan Wizard Progress
When you see only green check boxes, you have successfully configured this part of the maintenance plan. Excellent work so far!
Make sure that you update the job by double-clicking the job in the Jobs folder under SQL Server Agent. I changed the name of mine to trn Files, but you can pick another name if you want. After you change it, your interface should look something like Figure 6-19.
Figure 6-19. SQL Server Agent Jobs
Do you remember that we created two sections in the maintenance plan to delete files? The first part was Backup Files, which let us delete .bak files. The second part was Logs Cleanup, which let us delete .trn files. The next part is to clean up the text files left over by the backup operation.
Text Files Cleanup
When SQL Server runs maintenance operations using SQL Server Agent, it creates text reports for troubleshooting or general light reading. The Text Files Cleanup operation we’re going to set up will allow us to clean those files out after a certain amount of time. This is fine because a well-maintained database alerts a database administrator when there is trouble, which is the only real purpose of holding on to the maintenance text files generated by the tasks.
Starting fresh again, just right-click the Maintenance Plans folder under Management and choose Maintenance Plan Wizard. Enter Text Files Cleanup as the Name, and something short and sweet in the Description box, and then click the Change… button to set the schedule. Change the Occurs drop-down to Daily and leave the rest alone, and then click OK. Your interface should resemble Figure 6-20.
Figure 6-20. Select Plan Properties
Click Next at this screen. You can choose Maintenance Cleanup Task from the interface, as shown in Figure 6-21.
Figure 6-21. Select Maintenance Tasks
Choose Maintenance Cleanup Task here and then click Next. You will then see the interface shown in Figure 6-22.
Figure 6-22. Select Maintenance Task Order
Click Next here, since we only have the one task.
Previously, we chose Backup files. This time, we’re going to choose Maintenance Plan text reports. This is because we needed to set up a different task to delete different types of files, if you recall.
We are going to set this to point at our Backups directory, because that’s where we saved the maintenance plan text reports. These were stored as .txt files (defined back in Chapter 2 for report options). Your completed interface should look like Figure 6-23 when you are finished.
Figure 6-23. Define Maintenance Cleanup Task
Going through the options, you can see that the folder is defined to our Backups location, and the .txt file extension specified. Remember that this is the default extension for text reports generated by SQL Server. Also note that I changed the time selection to three days instead of four weeks. What does this mean? It means that I want to delete all .txt files in the E:SQL ServerBackups directory and those files in the first-level subdirectories that are older than three days when the task is run.
Click Next when you are ready and you will see the Select Report Options interface shown in Figure 6-24. Modify it as we did before (to write the detail log to our Backups directory) and alert us by email.
Figure 6-24. Select Report Options
Once you see this, click Next and review the summary. You should see what is shown in Figure 6-25.
Figure 6-25. Complete the Wizard
Go over these options carefully. Everything looks good, so click Finish again. You should (hopefully) see what is shown in Figure 6-26.
Figure 6-26. Maintenance Plan Wizard Progress
It’s a thing of beauty.
Again, make sure that you change the job name as described earlier—and update the job while you’re at it. I named mine txt Files. It’s up to you what you want to name yours, as long as it is easily recognizable. When you are finished, you should see the Jobs layout shown in Figure 6-27.
Figure 6-27. SQL Server Agent Jobs
Your Maintenance Plans folder should appear as shown in Figure 6-28.
Figure 6-28. Maintenance Plans
This all looks really good so far. Notice how we have separated each task into its own maintenance plan or task. This is going to be important in later chapters.
Let’s review what we did in this chapter, because this was perhaps the one chapter that could really screw up your database.
Summary
We learned that there are two different types of maintenance cleanup tasks that can be run: one that cleans up the files necessary for restoring data to the database (.bak and .trn files), and one for the maintenance plan text reports (.txt files). We also created three maintenance plans: Backups Cleanup (which deleted the .bak files), Logs Cleanup (which deleted the .trn files), and Text Files Cleanup (which deleted the .txt files). These three maintenance plans are going to be run separately and will delete the files specified by the individual plan.
If any part of this chapter is unclear, I urge you to go back and redo the exercises so that you can really get a grasp of what is going on and why. I want to take a moment again to stress how important it is to understand exactly what is being backed up and deleted, and why. It makes no sense to have a backup plan that constantly fails, so if you see failures in yours, troubleshoot them until you find the issue. Work to resolve them as soon as possible, because if you can’t restore the data that you’ve been entrusted to protect, you are pretty much redundant. This concept is absolutely vital to the success of any database administrator.
Tip
Theory is important, but unless it’s put in practice, it is almost useless.