Variable history sizes in SQL Agent Jobs

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

Advertisements

About davidbridge

I am a contractor for David Bridge Technology Limited specialising in database design and Web development using Microsoft technologies such as c#, MVC .net and SQL Server (full stack)
This entry was posted in Developer stuff, SQL Stuff and tagged , , , , , , , . Bookmark the permalink.

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