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 .
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
Anonymous
February 18, 2014
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 )Anonymous
February 18, 2014
Nice Post. Waiting for many more blogs :) of yoursAnonymous
February 20, 2014
Thank you so much Papy! Im glad you found it useful :)Anonymous
June 04, 2014
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 perfectAnonymous
February 09, 2015
Thank you. It was very usefulAnonymous
February 17, 2017
Thank you very much for sharing all this knowledge. It has helped a lot and saved time.Anonymous
June 15, 2017
It was very Helpful. I underwent the same situation. Explanation was clear