The job History of all SQL Server Scheduled jobs are getting deleted automatically on a daily basis!

  • Article

In this blog I would be explaining how we resolved a Scenario where a customer reported that

“The job History of all her SQL Server Scheduled jobs are getting deleted automatically on a daily basis”

In this scenario the Job history of the scheduled SQL agent jobs was only being retained for 2 days, when it was actually set to retain the history for 6 weeks.

Since the customer did not have the job history of all her jobs, she could not tell if a SQL agent job ran successfully or not.

Here’s what we looked into to nail this issue!

  • We made sure the retention period for the Job history was set correctly

Automatically remove agent history" settings [right click on SQL Agent | Properties | History]

This was set to retain 6 weeks of Job history . All other settings for SQLAgent were set to default.

  

  • We next checked to see if we have a Job that is perhaps purging the job history?

sp_purge_jobhistory stored procedure in msdb database can be used manually to delete the SQL Server agent job history

--This wasn’t being run on the customer’s server!

 

  • So we created a trigger to check what application is deleting the job history

      

       From the output of the trigger the Application name showed

SQLAgent - Step History Logger

             SQLAgent - Job Manager

 

  • So the sysjobhistory data was getting deleted under the application name SQLAgent - Job Manager

And the transaction captured by the trigger showed that the following command

EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory was being run periodically

  •   We checked sysjobsteps table to understand if any Scheduled job ever ran the following command

EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory 

 

  •  We searched for the text of this job and saw that in this job there is call to another stored procedure that is:

EXECUTE msdb.dbo.sp_jobhistory_row_limiter

  •        So first step was to find where this job history row limitation or restriction was stored!

We looked at sp_jobhistory_row_limiter text and found the below

 

Get max-job-history-rows from the registry

EXECUTE master.dbo.xp_instance_regread

N'HKEY_LOCAL_MACHINE',

                                         N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',

                                         N'JobHistoryMaxRows',

                                         @max_total_rows OUTPUT,

                                         N'no_output'

 

  -- Check if we are limiting sysjobhistory rows

  IF (ISNULL(@max_total_rows, -1) = -1)

      RETURN(0)

  -- Check that max_total_rows is more than 1

  IF (ISNULL(@max_total_rows, 0) < 2)

  BEGIN

    -- It isn't, so set the default to 1000 rows

    SELECT @max_total_rows = 1000

    EXECUTE master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',

                                            N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',

                                            N'JobHistoryMaxRows',

                                            N'REG_DWORD',

                                            @max_total_rows

  END

 

  -- Get the per-job maximum number of rows to keep

  SELECT @max_rows_per_job = 0

EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',

                                         N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',

                                         N'JobHistoryMaxRowsPerJob' ,

                                         @max_rows_per_job OUTPUT,

             N'no_output'

 

  -- Check that max_rows_per_job is < = max_total_rows

  IF ((@max_rows_per_job > @max_total_rows) OR (@max_rows_per_job < 1))

  BEGIN

    -- It isn't, so default the rows_per_job to max_total_rows

    SELECT @max_rows_per_job = @max_total_rows

    EXECUTE master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',

                                            N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'

N'JobHistoryMaxRowsPerJob',

                                            N'REG_DWORD',

                                            @max_rows_per_job

  END

 

 

¨  So we first read the entry from the following location in the registry:

                  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\SQLServerAgent\JobHistoryMaxRows          

(The value was set to 1000 in our customer’s box)

     HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R\SQLServerAgent\JobHistoryMaxRowsPerJob

This registry value can be controlled by the following option in the GUI .

clip_image002

 

Observations:

¨ We see that we are able to have only 1000 rows in the sysjobshistory table

 

¨ When we reach near this value in the sysjobshistory table, we see that the History is being deleted so we can retain the number of rows as 1000, even though the Retention period is set to 6 weeks.

   

¨ Changing the value in the Management Studio [ Right click on SQL Agent | Properties | History] to a higher number should allow the log to grow upto that much.

 

¨ We changed the Maximum Job History Log size to 15,000

 

Voila!! The customer reported that they are no longer seeing the issue!