SQL Server agent history

When installing a new SQL Server with SQL Agent, the default history for the agent is 1000 total items and 100 items per job. Is this adequate?

Imagine you have a server with 10 jobs that execute daily and each logs 1 history item per job run. You can only keep 100 lines of history per job which is just over three months history per job. So far this may not seem like a problem but what happens when you create a job with multiple steps or a job that runs every hour or several times per day?

The number of history steps goes up and whilst the total of 1000 for all jobs is adequate on 10 daily jobs of a single step, once you go over this the total limit means that some job histories may well be empty.

Why is this?

Imagine you have 9 daily jobs and 1 hourly job. The hourly job provides a history item 24 times per day so it can only achieve about 4 days of logs. If you up the history per job to overcome this to say 1000 then this job will use this amount in 41 days but when it does the max history lines will be exceeded and the daily jobs will not be able to log their details.

Consider the values carefully as the data is held in the msdb and therefore too much history will make the msdb big and slower but in my opinion the defaults are probably a little small for most production servers and probably also most dev servers.

BTW. I normally set them to 100,000 and 1000 without any issues and choose a max history of either 3 or 6 months and have never notice a performance problem with these values.


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 SQL Stuff, Uncategorized 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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s