SQL Server Maintenance Plans Maintenance Cleanup Task

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…":

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:

new maintenance plan

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.
new maintenance plan

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:

new maintenance plan schedule

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:

backup files

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

exeucte maintenance plan

The task is successfully executed:

exeucte maintenance plan

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

backup files

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:

backup files

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:

maintenance plan cleanup task

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

backup files

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:

maintenance plan cleanup task

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

picture files

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:

maintenance plan cleanup task

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

maintenance plan error

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.

maintenance plan logging and reporting

We can see the default path below.

maintenance plan logging and reporting

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

maintenance plan report files

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:

maintenance plan cleanup task

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:

Press ESC to close