[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)
