The Windows Task Scheduler is a useful tool for automating various tasks within the Windows operating system. It provides a simple graphical interface and is much easier to navigate than command prompt or Powershell. Though it does have some limitations when compared to the aforementioned options, there are still numerous actions that can be automated using the Task Scheduler.
One of the most useful tasks that can be automated, at least from my perspective as a Database Administrator, is the ability to perform automated file deletion. In the example outlined below, I will be creating a scheduled task to run daily which will delete files from a specified directory that are older than 90 days.
To provide a little background on this, the example I will be working with pertains to an automated SQL runtrace Agent Job that I have running on the server. The job runs daily and runs a continual trace logging any transactions that take longer than 3 seconds. As a result, a new file is added to the directory every day. Though each file is only around 11MB, over time this can begin to add up and deleting these files eventually becomes a necessity.
The steps outlined below will walk you through the steps of creating a new scheduled task which will call the ForFiles command to handle automatically deleting the older files.
The first step is to log into the server containing the directory from which you want to automate file deletion.
Once logged in, launch the Task Scheduler.
Once the Task Scheduler window appears, right-click Task Scheduler Library and select New Folder. Though this step is not technically necessary, I find putting related tasks into their own folder provides better organization when searching for a particular task.
In this example I will call the folder DBA.
Once the folder has been created you will see it listed under the default Microsoft folder. The next step is to right-click the newly created folder and select Create Task.
The Create Task window will open. On the General tab you’ll want to provide a Name for the task. Here you can also change the User assigned to run the task. In this example I am using my account but best practice would suggest using a proper service account which has sufficient permissions on the server.
You will also want to select the radio button next to Run whether user is logged on or not. This will make sure that the task will be called regardless of the the user’s connectivity to the server.
Next, click on the Triggers tab. This is where we will configure when the scheduler will run. Once on the Triggers tab click the New… button at the bottom of the window.
The New Trigger window will be displayed.
As seen in the screenshot below, I have configured this trigger to begin on a schedule and to run on a Daily basis at 7:00PM.
Once you have configured your trigger click OK.
You should now see your newly created trigger listed.
Next, click on the Actions tab and click New… at the bottom of the window.
The New Action window will be displayed. This is where you will call the ForFiles script and configure the command line argument to delete the files. This is the most important step.
Make sure the Action being called is set to Start a program. This should be the default setting but if not, just select it from the drop-down.
Next enter ForFiles into the Program/script text box.
The final step requires you to specify the argument that you want to pass to the ForFiles script.
In this example I am passing the following argument:
/p "C:\DBA\SQLTrace" /s /d -90 /c "cmd /c del @file"
Depending on your specific needs, you will need to change the directory being specified as well as the number of days back. The above argument will remove files older than 90 days from the C:\DBA\SQLTrace folder.
Once you have configured the argument click OK.
You will now see the newly created action listed.
There are two additional tabs, Conditions and Settings. Though it is not necessary to change any of the default configuration options on these two tabs, they are worth reviewing. Depending on the scheduled task you are creating, some changes may be beneficial.
Though I rarely do anything with the Conditions tab, the Settings tab does have a couple options that I find to be useful. Though I am not selecting them for the purposes of this example, the highlighted options below can be useful depending on the task being scheduled. You can also instruct the Task Scheduler to self-delete if it has not been called for a particular number of days.
Once you have fully configured the task and any additional options click OK.
You will be prompted to enter the necessary credentials based on the account you selected to run the task when configuring the General tab. Once the credentials are entered you will see the newly created scheduled task listed under the task library folder you created.
If you wish to manually execute the task to test and make sure it performs as intended you can do this by right-clicking the task and select Run.
