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


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!


    Comments (5)

    1. Papy Normand says:

      Thanks for this excellent 1st post.

      It remembered me this thread of which I have kept the track as it was not in the good forum

      social.msdn.microsoft.com/…/sql-server-2008-not-recording-logs

      Difficult to find as it was found in the SQL Server Express Forum ( edition not supporting SQL Agent !!!

      Your explanations were clear and easy to understand even for me who is a beginner with SQL Agent.

      I hope your next posts will be as pleasant as this one ( I need articles like this one )

    2. Nice Post. Waiting for many more blogs 🙂 of yours

    3. Pooja Kamath says:

      Thank you so much Papy! Im glad you found it useful 🙂

    4. Shanky_621 says:

      Your solution was fine but intermediate steps you took needs to be explained in more details. I was aware about the issue so just went back directly to your solution section.But for user who is facing issue first time your intermediate steps which you used to actually drill down to issue( which were actually good) needs more explanation. if you could add that perfect

    5. Ganesan says:

      Thank you. It was very useful

    Skip to main content