Maintenance Cleanup Task

You have been diligently backing up your database and transaction logs, but now you have a directory that is full of files that have been backed up to disk/tape already or are no longer needed since they are old.  Within SSMS, you are able to create a job that can delete out the files that are no longer needed.

 

Create a Directory Cleanup Task

We will use the Maintenance Plan Wizard again, so right click: Maintenance Plans, and then select Maintenance Plan Wizard.

 

 

This will bring up the Maintenance Plan Wizard title screen, press next.

 

 

We can now name our job, so we name it something original like: Directory Cleanup

 

 

For type of task to create, select Maintenance Cleanup Task, then press next.

 

This will be the only task in this job, so press next.

 

Now we get to tell the wizard what we want to delete and what directory and file extension we wish to delete.  Below, the folder is what directory you want to delete files from.  We want to delete all files in that directory so we use *.* to get rid of all files.  Sometimes you just want to get rid of the trn or bak or txt files, so you would just put that extension in.

The last important setting on this page is how old of files do you want to delete.  If you have plenty of storage, you can keep multiple days of files, but for now, we will get rid of any file over 1 day old.  You can choose hours, days, weeks, months and years.  Then click next.

 

We then tell it where we want to store the log file for the job, we will just write the results to a file.  Press next.

 

This screen shows us the settings we choose for our job, press finish.

 

That creates the Job, now we need to schedule it.

 

Scheduling the Directory Cleanup Job

Double click the Directory Cleanup job that we just created.

 

This opens up the job properties screen.  Click on the Schedules page, and then click New.

 

We want this job to run once a day at 3:00am, so the below settings will accomplish that.  You will need to determine when is the best time to delete files and work it around your backup times.

 

That creates the schedule as seen below.

 

Lets run the job and see if it works.  Right click the Directory Cleanup job under the Jobs section of Object Explorer in SSMS.  Click Start Job at Step.

 

That will start the job and will alert you to any errors.

 

After the job completes, you can see below, it deleted all of the files over 1 day old.

 

This completes how to create and schedule a maintenance cleanup task. This is a very handy task, but you really need to be careful that you are deleting from the correct folder and that you are deleting old files so you do not get rid of anything that you may still be needed or not backed up to disk/tape yet.