Default Job History Limits
Recently I needed to diagnose why a weekly, multi-step SQL Agent Job was taking so long. I thought, no worries, I’ll go and look at the history to see which step it was. Except…there was no history.
Looking at the Job Activity Monitor, I could see that the Last Run was Saturday morning, and the output of the job was there, I knew that the job was running, but when viewing the Agent History, it showed no history.
Digging further, I noticed that it wasn’t just the history of this job missing, but almost all jobs were missing their history or only had 1 or two runs in them.
I went looking at the sysjobhistory table in MSDB and noticed that there were exactly 1,000 rows. Whenever there is a nice round number like 1,000 in a result set, I’m always suspicious, and this time I was right to be.
Digging through the SQL Agent properties, there is a section called history which has a Maximum job history log size of just 1,000 rows.
This server was doing log backups every 5 minutes, meaning every day that job alone was creating 288 records. Once you throw in all the other jobs, there was over 500 entries created per day.
This data is stored to a SQL table in MSDB. A SQL history table with 1,000 rows is tiny, there is no reason to keep it this small on a modern server. I have mentioned before that you can run in to performance problems when you don’t clear our your maintenance history, but pick a bigger number than 1,000.
Changing the numbers was easy in SSMS using the screen above, but can also be done by calling the undocumented sp_set_sqlagent_properties
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows = 1000,
@jobhistory_max_rows_per_job = 100