Create MS SQL Maintenance Cleanup Task

This is the fourth part of MS SQL backup maintenance plan tutorial, and continues from Create MS SQL Shrink Databases Task one.

In this tutorial we will discuss how to create and setup Maintenance Cleanup Task in MS SQL for outdated database backups and transaction logs.

The Maintenance Cleanup task removes files related to maintenance plans, including database backup files and reports created by maintenance plans.

By using the Maintenance Cleanup task, a package can remove the backup files or maintenance plan reports on the specified server. The Maintenance Cleanup task includes an option to remove a specific file or remove a group of files in a folder. Optionally you can specify the extension of the files to delete.

I will create two cleanup tasks. Drag two Maintenance Cleanup tasks one for the databases and one for the transaction logs.

Create MS SQL Maintenance Cleanup Task

Right click on the first Maintenance Cleanup task and choose ‘Edit’.

Fist you have to setup the connection to the server as in the previous tasks, then for ‘Delete files of the following type’ select ‘Backup files’.

Check ‘Search folder and delete files bases on an extension’ and for folder choose the same location set in the MS SQL Back Up Database Task. Write the extension without the dot! So, in this case this will be ‘bak’ as on the screenshot.

As I am using separate folders for each database, I will check this option for ‘Include first level sub-folders’.

I will set the database backup files to be deleted after four weeks so I will check ‘Delete files based on the age of the file at task run time’ and will set the period I intend. Click OK and we are done.

Create MS SQL Maintenance Cleanup Task

On the second Maintenance Cleanup task I will do almost the same but instead of ‘bak’ files, this time it will delete old transaction log files, and the extension will be ‘trn’.

Again, it is important to set the extension without dot!

Create MS SQL Maintenance Cleanup Task

Ok, we are ready with the MS SQL backup maintenance plan so let’s save the tasks first.
For that right click on the name at the top as it is shown on the next image.

Create MS SQL Maintenance Cleanup Task

Then select the maintenance plan at right inside the ‘Object Explorer’ and choose execute.

Create MS SQL Maintenance Cleanup Task

Well that is it. We just started the plan for a first time and it will start serving from now on to keep the data safe.

Tags

Filed Under: MSSQL

Anthony Gee About the Author: Anthony G. is an IT specialist with more than 9 years of solid working experience in the Web Hosting industry. Currently works as server support administrator, involved in consultative discussions about Web Hosting and server administration. One of the first writers in the Onlinehowto.net website, now writing for Free Tutorials community - he is publishing tutorials and articles for the wide public, as well as specific technical solutions.

Comments (2)

  1. Accelero says:

    Where did we back up the trn’s that we’re deleting?

  2. Brian says:

    Great tutorial! I was getting annoyed enough by the fact that the sub-directory’s databases and transaction logs would not delete, that I decided to do some research and came across this site.

    @Accelero The trns are backed up to where ever you specified when you setup that particular maintenance plan.

Leave a Reply