Recently I came across a strange issue while dealing with sql server agent. The issue was in one of our recently implemented projects which was dealing with population of data warehouse with data from heterogeneous sources. The population was done by means of batch jobs which used to execute daily to bring delta changes of data. We spend couple of hours on this before realizing what happened. So thought of blogging this so that people could know about this just in case they’re not aware.
The scenario was like we’d couple of webpages which used to show the batch job run status. This was used by operations team to monitor status of job running and raise alarm in case of any problem. The issue was like some of the jobs where not getting listed in the pages. The strange thing was the jobs were getting missed in random. I’d a look at history of the missed jobs in SQL agent and noticed that it was also missing history for the day. Then I’d a look at the sysjobs and sysjobhistory tables in msdb database of the server to find that history is present there! This really baffled me as i couldnt understand why it was not getting displayed in view history tab of SQL server agent. After spending sometime checking the properties of SQL Server agent I found the problem. The cause of this problem was two settings of SQL Server agent. They’re maximum job history log size and Maximum job history rows per job. These two settings determine number of rows to be shown in total in SQL Server agent and also number of rows per job respectively. The default values of them were 1000 and 100 which means it could show only history of 10 jobs at a time. We had more jobs which caused some of history rows to be missed from history. Setting them to higher value solved issue.
