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:
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:
- https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/maintenance-plans?view=sql-server-2017
- https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/create-a-maintenance-plan?view=sql-server-2017
- https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/use-the-maintenance-plan-wizard?view=sql-server-2017
- https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/history-cleanup-task-maintenance-plan?view=sql-server-ver15
