This is the first in several posts that will discuss quick tips and tricks for managing Sql Agent history. Today, I’m going to discuss one of the history management options which will let you delete history. There is an easy way to do this, but it has some limitations to be aware of, which I will point out below.
The first thing to do is to find the Sql Server Agent node in Management Studio, and right click on it to open the Properties. Select History from the right hand “Select a page” section. This opens up the dialog shown below. Note the checkbox below to “Remove agent history”. You can specify the time period for deletion, as shown below.
When you click OK to dismiss the dialog, the history data which is older than the specified time period will be deleted. However, this is not a recurring behavior. It is a one time only operation- and that has caused some customer confusion. The next time you revisit the dialog, the checkbox is gone. One way to get recurring behavior is to write a scheduled job. This is ultimately more flexible than using the quick history delete feature below. This is also pretty easy to do- you can get started by simply clicking the Script button (see dialog below) and select “script to job”. Now you have a job that will delete history older than a certain date. All you need to do now is create a schedule for the job to run.