Tips and Tricks : SQL Agent Job history data missing?
Posted by blakhani on September 18, 2014
I have many good friend who call me only when they have SQL Server related problem to solve. I really love you guys (in a manly kind of way!) for remembering me, at least during problem time.
One fine day my DBA friend called me and said that they have a data missing problem. There is a job which is suppose to move the data from one table to another. Application team reported that job didn’t run on one specific time as per their data calculation. As per their logic they pull hourly data from Table1 to Table2. They have observed that 1 hour data is missing. When they looked into job history they don’t see any evidence of job history. Oldest record for this job is 2 hours back.
My immediate question was – do you have many jobs running on the server? As expected, answer was Yes! They had may such data movement jobs and few of them run every 5 minutes. I asked him to run below command
select count(*) from msdb.dbo.sysjobhistory
and answer was 1000. And I said – that is THE problem! The SQL Agent Job system limits the total number of job history on per job basis as well as overall basis. This information is stored in the MSDB database. I asked him to open SQL Server Agent Properties and go to History page. Over there Modify the ‘Maximum job history log size (rows)’ and ‘Maximum job history rows per job’ to suit, or change how historical job data is deleted based on its age.
I also told that this won’t give you back the history, but it’ll help with your future queries! He was happy with the answer.
Later I did more research and found that this can also be done using below T-SQL command
USE [msdb] GO EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=1000, @jobhistory_max_rows_per_job=100 GO
If there is a need to purge the data manually using UI then we can use “Remove Agent History” checkbox and define the duration. The equivalent of that below
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='2014-09-14T09:02:37' GO
We need to provide the oldest date in T-SQL
Hope this helps!
Help: SQL Server 
Jay said
If we increase the default size of and retention of Job history is that not going to hit the performance of the MSDB and hence inversly the job performance.?
blakhani said
cleanup is a part of post job completion. Why would it impact performance?
Vivek said
Nice Article, Balu Sir. We often configure Jobs, but always overlook this part of the job.
blakhani said
Thanks!
Dave said
Gooԁ web site you have got herе.. It’s hard to find excellent writing like yours these days. I honestly appreciate individuals like yߋu! Taҝe care!!
Manoj said
We have 100 + jobs running in SQL Server Agent. But one particular job history is missing as it keeps only 1 day log. Why is this happening ?
Ravi said
Thanks Balu Sir , this was useful 🙂
Sweta Yadav said
Thank u Balu Sir.. U r the best 🙂
blakhani said
Thanks @Sweta