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.