SQL Server Maintenance Plans Maintenance Cleanup Task

By: Sergey Gigoyan | Updated: 2020-03-02 | Comments | Related: > Maintenance

Problem

In a previous article, we discussed the History Cleanup Task, which cleans old historical records in the msdb database generated by Maintenance Plans, Backup and Restore operations and SQL Server Agent Jobs.

As a result of the usage of Maintenance Plans, historical data is collected not only as records in the msdb database, but also as operating system files. Particularly, each backup database task in Maintenance Plans creates a backup file, which is stored on disk. If the task schedule is intensive and the size of the databases is big, the storage space can become an issue quite quickly. In addition, over time these backup files become outdated, so storing it on disk becomes meaningless. Therefore, it is reasonable to periodically delete these files and release storage space.

The second type of files generated by Maintenance Plans are report files. These are text files and have smaller sizes and are generated quite often (for each maintenance plan run) if the corresponding setting is set to "ON". This setting is "ON" by default, and therefore these files will be generated if we do not uncheck the checkbox of recording the log when configuring the maintenance plan. Hence, these files should be removed periodically in order to avoid unnecessary use of storage space and also to just keep things tidy.

Solution

The Maintenance Cleanup Task is specifically used for removing obsolete files generated by the use of Maintenance Plans. It allows us to remove full, differential, transaction log backup files and report files from the provided location based on the file extension. It is also possible to remove a specific, single file. However, it is only possible to remove one type of file in each Maintenance Cleanup Task. In other words, you can only use one type of file extension in the configuration to remove those specific file types. Therefore, it is not possible to delete, for instance, transaction log files (with .trn extension) and full or differential backup files (with .bak extension) extension within a single task. You would need to create two tasks.

In this article, we will demonstrate two Maintenance Cleanup Tasks – the first one for removing the backup files and the second for removing the maintenance report files.

Deleting old SQL Server Backup Files with Maintenance Cleanup Task

Here, we are going to create a demo of the Maintenance Cleanup Task using the Maintenance Plan Designer.

To do so, right-click on Maintenance Plans under Management in SSMS and choose "New Maintenance Plan…":

Then, we choose the name for our plan. As the first task to be configured to delete backup files, we have chosen the corresponding name:

When the Maintenance Plan Designer opens, we drag and drop the "Maintenance Cleanup Task" to the "Design Surface".

Then double-click on the task to configure as shown below.

First, we select Backup Files for the files to delete.
We specify the folder where the backup files are stored.
In the File extensions box, we specify "bak" to delete files with the .bak extension. If we want to delete transaction log backups we would need to specify "trn".
Lastly, we specify that we want to delete files that are older than 2 weeks old.

In the image above, if we click on "View T-SQL", we can see that the xp_delete_file procedure is executed for this task. This is an undocumented procedure and in this article, we will perform some tests to understand how it works.

Below is the schedule to have this run weekly:

After configured the task, we click on the "Save" button (or Ctrl + S) to save it.

If we also want to delete transaction log backups, another task would need to be created and specify "trn" as the file extension.

Testing the Maintenance Plan

Before executing the task, we open our backup folder to see the existing files. In our environment, the selected files should be removed as a result of the task execution, as they are older than two weeks:

Now, we right-click on the task and click on "Execute":

The task is successfully executed:

When we open our backup folder again, we can see that the above-mentioned files have been deleted as expected:

Delete files that are not SQL Server backup files using Maintenance Cleanup Task

Now, let's do some tests. Let’s see what happens if we try to delete non SQL Server backup files. We have added pictures to the backup folder with the .png extension:

Then, we edit the configuration of the task and change the extension from the bak to png and uncheck the checkbox related to the file age. This means that in the case of successful completion all png files will be deleted:

When we run the modified task again, we can see that it successfully finished, but the png files were not removed.

Now, let's do another test where we change the extension of a real backup file to .png from .bak and execute the task again.

The task is successfully completed and TesDB_Backup_2019_10_29_013003_1611653.png is removed, but Picture1.png and Picture2.png have remained as shown below.

Additionally, let's change the extension of one of the images to .bak and set the extension in the configuration to bak to see whether the picture will be removed or not:

The task is successfully completed, but only real backup files are deleted and the png images remain:

This means that despite the mentioned extension, only real backup files are removed (if the extension matches the mentioned extension). If we try to remove the specific file which is not a backup file (even if it has .bak extension), the task will generate an error:

As we can see, it is not possible to remove files as backups if they are not real backup files:

Deleting text report files with Maintenance Cleanup Task

To find where these files are located, in a Maintenance Plan click on the Reporting and Logging button as highlighted below.

We can see the default path below.

We can see the files in this path "D:\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log".

To remove these old files, we have created a new plan called "DeleteOldLogFiles".

In the configuration, we have chosen "Maintenance Plan text reports", added the path above in the "Search folder:", and set "txt" as the file extension:

We have chosen to remove all report files older than one hour in our example. After saving and executing the plan, we can see that only a few report files (generated during the last hour) remain:

maintenance plan logging and reporting files

Conclusion

To sum up, we have configured and tested the Maintenance Cleanup Task, which is used to remove old backup and maintenance report files. This is very important in terms of releasing space and avoiding an overload to disks with useless, outdated files.

Next Steps

To read more about the topics discussed above, please use the links below:

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2020-03-02