[Note from Pinal]: Everybody knows what is SQL Agent Jobs. We know that they run at regular interval and does the task assigned. However, there are way many more things we should know about SQL Jobs but do not know it or never learnt to care about it. One of the most essential elements of SQL Agent Job is history.
In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) discusses about SQL Agent Job History in this blog post. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.
In this tip of the week, I want to cover a common problem I see too often in the field. Multiple times I have been asked, “Dude, where is the SQL Agent Job History?” I had a job fail but I cannot view the history and get details towards why it failed. Today, I am going to cover why the job history is most likely disappearing on you.
Typically there are two reasons why your SQL Agent Job History is missing. First, someone or a process is running sp_purge_jobhistory to purge the history. Typically, I find that this isn’t the reason why your history is disappearing randomly. Normally, it’s the default settings for the SQL Agent Job History that is purging your history. In this week’s tip were going to focus on the cause and effect of the default settings focusing on why you might need to change them.
Setup Demo
This tip is going to utilize two SQL Agent jobs that you need to download if you want to walk through step by step through the demo. The first job is “JobHistoryDemo – Divide by Zero” and the second is “JobHistoryDemo – Batch Process.” In this demo, were going to execute a job that will fail (Divide by Zero) and then were going to execute another job (Batch Process) over a 1,000 times. For the purpose of this demo, the batch process job just does “SELECT 1” from tempdb.
For the purpose of this demo, were going to tweak the default setting for max rows per job. By default, this is set to 100. We are modifying this to be 1000 so we only need two jobs for the purpose of this demo. We will go into more detail about SQL Agent properties later in this tip.
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows_per_job=1000
GO
Run Demo Code
After downloading and running the scripts you will have the two new “JobHistoryDemo” SQL Agent Jobs shown below.
Run the following script it will take about 30 minutes to complete. You will notice that it will start to automatically purge the history for all the SQL Agent jobs. We will dive into why during the next section of the tip.
/* Job fails */
EXEC sp_start_job @job_name = 'JobHistoryDemo - Divide by Zero';
DECLARE @i INT;
SET @i = 1200; -- default history retention for job history records
WHILE @i > 0 BEGIN
EXEC sp_start_job @job_name ='JobHistoryDemo - Batch Process'
WAITFOR DELAY '00:00:02';
SET @i=@i-1
END
If you want to monitor the progress you can execute the following script. You will notice that the history gets purged once the “JobHistoryDemo – Divide by Zero” executes 1,000 times.
SELECT j.name, COUNT(*) Executions
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id
GROUP BY j.name
ORDER BY Executions DESC
Here is a screen shot just right before we hit 1,000 executions.
Here is a screen shot just right after we had a 1,000 executions. Notice, that just 1,000 total SQL Agent job executions triggers the purging process. It doesn’t have to be limited to just a specific job.
Where did the history go?
Now we have the scenario described in the first paragraph of this tip. In this scenario, we have a controlled scenario where job history is missing. This can be very frustrating if you have multiple jobs executing frequently as it will seem like the data is being purged randomly.
Let’s take a look at the SQL Agent Job Activity monitor. We can clearly see that the “JobHistoryDemo – Divide by Zero” job failed. Right click on the job and select view history.
You will notice all the job history is gone. Next we will show you most likely why you don’t see the job history.
How do we resolve the problem?
The first step to solving the problem is to identify the root cause. Reading along you might already know why the history is gone. Let’s drill down to the properties of the SQL Agent and focus on the history tab.
You will notice that the default setting is to only keep the last 1,000 history records for all the SQL Agent Jobs. The SQL Agent will also only keep 100 history records for a single job. It’s why we modified the Maximum job history rows per job property above for this demo.
Now that we have identified a root cause to this problem here is how you resolve it. Disable the limit size of job history log and utilize sp_purge_jobhistory to purge as needed to meet your storage and business requirements for maintaining agent job history.
Finally, if you need to maintain the results of SQL Agent jobs I recommend using the native functionality built-in to store message details. We will cover this in a future tip.
Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
I find it more likely that you would want to change the default number of total history records stored. I wouldn’t ever want to just purge the history from the entire server. I’ve found 10,000 records is a good place to start. Keep 100 for jobs that run 1-3 times a day. 1000 for jobs that run once an hour. If your jobs are running significantly more often than that, you should bump the total number up to 100k.
Of course, you don’t need to use the SSMS UI to change the job history retention settings. You can do it from a command as well, making it possible to add it to scripts. The code I use for my servers is typically like this:
— Update Agent Job History retention to 60 * 24 * 7 (one record per minute for one week) for each job
— and ten times that as the maximum total number of records.
EXEC msdb.dbo.sp_set_sqlagent_properties
@jobhistory_max_rows=100800,
@jobhistory_max_rows_per_job=10080;
You may need different values, depending upon the number of jobs you run and how often they run. We do have jobs that run every minute, but certainly not 10 of them. But this gives us plenty of extra room at very little cost.
I need a different manner to keep history. I have BizTalk where several jobs MUST run every minute, and call other jobs all of which may/may-not have several steps. YET, I need to review the massive amount of history for just SQL full/simple backups AND BizTalk DEFAULT Full backups. not currently working to my satisfaction. then again, biztalk help is far and in between.
Hello.
I am trying to solve in what I would have thought would be a more common problem. However, I am not seeing anyone online attempting to do this.
I have a powershell script that will query all of our servers in the enterprise to capture the SQL Server Agent Job History. What I want is to identify and eventually delete old, unused Agent Jobs that have been hanging around for a long time (10+ years in some cases).
However, I am noticing that [msdb].dbo.SYSJOBHISTORY does not contain all the data that I see in the GUI for the last time the job Ran. For instance, If I check the GUI properties of some jobs, it displays a last execution time. My query Displays it as null even tho the GUI shows me the value. I am not sure if I am doing something wrong or if this is simply not possible to accurately capture this information. Below is my Query. Even when I just do a select * from [msdb].dbo.SYSJOBHISTORY – The Job does not show at all, when it does indeed have a timestamp in the jobs properties.
SELECT
[sJOB].[name] AS [JobName]
, [sDBP].[name] AS [JobOwner]
, [sCAT].[name] AS [JobCategory]
, [sJOB].[description] AS [JobDescription]
, CASE [sJOB].[enabled]
WHEN 1 THEN ‘Yes’
WHEN 0 THEN ‘No’
END AS [IsEnabled]
, [sJOB].[date_created] AS [JobCreatedOn]
, [sJOB].[date_modified] AS [JobLastModifiedOn]
, MAX([msdb].DBO.AGENT_DATETIME(RUN_DATE, RUN_TIME)) AS [Last Time Job Ran On]
, [sSVR].[name] AS [OriginatingServerName]
, [sJSTP].[step_id] AS [JobStartStepNo]
, [sJSTP].[step_name] AS [JobStartStepName]
, CASE
WHEN [sSCH].[schedule_uid] IS NULL THEN ‘No’
ELSE ‘Yes’
END AS [IsScheduled]
, [sSCH].[name] AS [JobScheduleName]
, CASE [sJOB].[delete_level]
WHEN 0 THEN ‘Never’
WHEN 1 THEN ‘On Success’
WHEN 2 THEN ‘On Failure’
WHEN 3 THEN ‘On Completion’
END AS [JobDeletionCriterion]
, [sSCH].[Schedule_ID]
–INTO PB_SQLAgentJobInfo
FROM
[msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
ON [sJOB].[originating_server_id] = [sSVR].[server_id]
LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]
ON [sJOB].[category_id] = [sCAT].[category_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]
ON [sJOB].[job_id] = [sJSTP].[job_id]
AND [sJOB].[start_step_id] = [sJSTP].[step_id]
LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
ON [sJOB].[owner_sid] = [sDBP].[sid]
LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
Left JOIN [msdb].dbo.SYSJOBHISTORY JH
ON sJOB.job_id = JH.job_id
Group By
[sJOB].[name]
, [sDBP].[name]
, [sCAT].[name]
, [sJOB].[description]
, [sJOB].[enabled]
, [sJOB].[date_created]
, [sJOB].[date_modified]
, [sSVR].[name]
, [sJSTP].[step_id]
, [sJSTP].[step_name]
, [sSCH].[schedule_uid]
, [sSCH].[name]
, [sJOB].[delete_level]
, [sSCH].[Schedule_ID]
ORDER BY [JobName]
Joseph Giombi, I recently started auditing our jobs (Sql Server 2012) for the same purpose of removing old unused jobs and have noticed the exact same issue.
The interesting thing is that I have the same GUI issue as you (where I have a Last Executed date on the job properties page, but no sysjobhistory entries) for jobs that have ran as recently as last month (and continue to run once every month), but I do still have sysjobhistory entries for some jobs that haven’t even run in the past year.
If I find anything more out I’ll be sure to report back here…. did you figure anything out since you posted this?
Job history not shows any history data because of the issue in MSDB system database. If we could restore the same version of MSDB database from the earlier saved MSDB backup or the backup retrieved from some other machine then it will definately solve the problem.