Skip to content

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.

Comments

  1. Dominic says:

    This helped me a great deal, many thanks and well done.