Brad’s Sure Guide to SQL Server Maintenance Plans- P36: SQL Server has a reputation as being a simple database application to install, configure, and maintain. This is a little misleading. SQL Server is a powerful relational database that can handle the needs of the largest organizations and, as such, its proper maintenance almost certainly requires the attention of an experienced DBA. | Chapter 15 Maintenance Cleanup Task SQL Server instances as often as transaction log backups are made. This way should your server have a complete disk failure you will have a full backup along with the most recent transaction log backups ready to be restored to a new server. The Maintenance Cleanup task within the Maintenance Plan Wizard is designed to remove older files that have been safely copied to a separate offsite location and which you no longer need to store locally. However the task doesn t work quite as you might hope or expect. While it has the ability to delete older report text files files with a TXT extension full and differential backups files files with a BAK extension and transaction log backup files files with a TRN extension this task can only delete one of these file types at a time from within a single Maintenance Plan created with the Maintenance Plan Wizard. Again this limitation arises because the Wizard is unable to create multiple tasks within the same Maintenance Plan. So while ideally you d like to clean up all these older files at once as part of a single Maintenance Plan you can t so you will need to find alternative ways to deal with this problem. While there are several different options you can choose here s what 1 recommend. First within your central Maintenance Plan use the Maintenance Cleanup task to delete the older report text files. Then in order to remove the older BAK and TRN files you have the options below. Create two additional Maintenance Plans using the Maintenance Plan Wizard with one plan used exclusively for deleting older BAK files and another plan used exclusively for deleting older TRN files. This requires you to create two additional Maintenance Plans but it works well especially if you are not familiar with T-SQL. Use the Maintenance Plan Designer to devise a single Maintenance Plan that will remove all three types of older files in one go. 1 explain how to do this starting in Chapter 16. Script a job to .
