SQL Server Agent job history getting lost or not populating
You may come across a scenario when all of a sudden you are not able to see the job history for almost all the jobs except few. Here are few things that will help you in analyzing the issue. Recently we have faced this issue in our environment below id how I manage to find the cause of this issue:-
• Connect to SSMS and open the job history by going to SQL Server Agent → Job → right click and view history, for all the jobs and check how many rows are returned. Like in this case it returned only 1188 rows.
• Now Go to SQL Server Agent → Right Click → Properties → history tab. Here check what is the maximum job history log size and maximum job history per job. In this example it is set to 15000 each. Default for maximum job history log size is 1000 and maximum job history rows per job is 100.
• Now connect to msdb database and run below query to find the row count in jobhistory table.
select COUNT(*) from sysjobhistory ----------- 15000 (1 row(s) affected)
• We have found that the history table is full. So now we need to check why it’s full and which job is writing huge data in history table. In order to find that you may have to run below query in msdb database
select b.name, a.job_id, a.run_date, COUNT(*) NumberofRun from sysjobhistory a, sysjobs b where a.job_id = b.job_id group by a.job_id, b.name, run_date name job_id run_date NumberofRun ---- ------ -------- ----------- Job1 R14UIUJI-C164-5FUI-BDAD-54C3UIUJIA2B 20130328 84 Job2 269UIUJI-5902-4E0C-5FUI-57AABAUIUJI2 20130328 12217 Job3 8UIU5FUI-6B0B-446C-9B64-6805FUIIUJIB 20130328 125 Job4 UIUJIB0F-EB63-5FUI-B03D-98B235FUIUJI 20130328 2480 Job5 43CUIUJI-5FUI-4CE2-B068-AEUIUJI19E3A 20130328 84 Job6 AUIUJI5A-8166-42ED-5FUI-FFUIUJI02CD8 20130328 10 (6 row(s) affected)
• The output of above query will help you find the jobs which have more rows in jobhistory table. Like in above example you will see that Job2 is adding almost 12K rows in history table in a day. So now we need to go to this job and see what it is doing.
You can alter above query to find the actual step which is sending huge data to history table. This will help you pin pointing the step which needs attention
select b.name, a.job_id, a.step_id, a.run_date, COUNT(*) NumberofRun from sysjobhistory a, sysjobs b where a.job_id = b.job_id group by a.job_id, b.name, a.step_id, run_date
• Now we know that the job history is getting lost because Job2 is just sending huge data to history table. In order to fix this we have to either look into the job step and modify the job so that it will not send huge number of rows to history table or consider changing the maximum job history log size and maximum job history rows per job to higher values, based on above analysis and frequency of troublesome job runs.