If you have lots of jobs then you will need to adjust the max history sizes in the SQL Server Agent but what if you have one or two jobs that run every few minutes which hog all the log resource?
One option is to massively increase the log size but this slows down MSDB and can cause locking.
A better option is to limit the log size on just the jobs that are creating a lot of entries but this is not an option in the management console.
Here’s how to do it
The following tip uses Agent Tokens and i recommend you read this article if you don’t understand them. http://msdn.microsoft.com/en-us/library/ms175575.aspx
Create a final job step with the following code.
DECLARE @cutoff datetime = dateadd(minute,-30,getdate())
DECLARE @Job_id uniqueidentifier = CONVERT ( uniqueidentifier, $(ESCAPE_NONE(JOBID)))
EXEC msdb.dbo.sp_purge_jobhistory @job_id=@Job_id , @oldest_date=@cutoff
This will look up the job id (guid) for the currently executing job using the token (JOBID) and calculate a date in the past which is the cut off date for history values shown here as 30 minutes. This would be useful for a job that runs every minute or so. Adjust to suit your own job frequency.
Now when the job runs the final step of the job is to purge its own history.
Cheers
Dave
