Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


No Job History Showing
Published: Jun 26, 2015
This is a common complaint I hear whenever I see a new server for the first time… I see a failed job, go to open the Job History, and am told “oh, there’s never anything in there. Why doesn’t SQL ever hold enough?”. Well, the answer is that it simply hasn’t been told to.

By default SQL Server doesn’t hold a very long job history for your server… but this is because the guys who wrote it can’t predict how you’re going to use your instance. But this is something that is very easy to rectify.

The defaults that SQL Server comes with are as follows:

If you have only 3 or 4 jobs which run once a day (maintenance, for example) then by default you’ll have lots of history available. In fact SQL Server would happily hold the last 100 runs for each job.

However, now imagine that you have a SQL Server which is running a maintenance job once a night at midnight, but also has 20 replication agents running on a 1 min schedule.

Well, based on this you can see that to reach the 1000 job history log maximum would take just 50 mins… therefore by 1am each morning the only run data for the maintenance job would have been deleted. This would make a failure very hard to track down.

This is the exact type of situation I have seen numerous times on servers and it’s a very easy thing to fix, BUT it does require a SQL Server Agent restart, you do NOT need to restart SQL Server itself.

What you need to do is to Right Click SQL Server Agent in SSMS and select Properties:

This will open up the Properties Dialog. From the options on the left of the window, choose History:

You can now see all the options that you need to change in order to maintain a good sized history.

This will need to be calculated based on the frequency of your jobs, how much history you wish to maintain, and also, don’t forget how large your MSDB is capable of growing because that’s where all the history is stored!!!

As an example, if you wish to hold 100,000 job history records, with at most 250 for any single job, and you want to make sure that no history is held longer than 2 weeks, then you would choose the following:

It’s as simple as that, and you no longer have an excuse to blame SQL Server for a lack of job history when something goes wrong.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.