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.

maint14

 

Create a Directory Cleanup Task

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

maint1

 

 

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

maint

 

 

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

maint3

 

 

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

maint4

 

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

maint5

 

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.

maint6

 

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.

maint7

 

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

maint8

 

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

maint9

 

Scheduling the Directory Cleanup Job

Double click the Directory Cleanup job that we just created.

maint10

 

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

maint11

 

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.

maint12

 

That creates the schedule as seen below.

maint13

 

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.

maint15

 

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

maint16

 

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

maint17

 

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.

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s