Problem
SQL Server exposes two settings for managing the SQL Server Agent log. One setting dictates how many log rows to keep, and this is broken out into options for any single job and for the agent subsystem as a whole. The second is a simple interval that dictates how old history data has to be before it is automatically deleted (measured in days, weeks, or months):
This is not very flexible and, the reality is, you’re probably reading this because you have jobs with different history retention requirements. Not only are some jobs more important than others, where you want to keep more history than others, but you also might want some kind of logic that dictates where either age or count is more important.
Consider a very simple case of full and log backups. If you perform a full backup daily or weekly, do you really need the history for the last 100 events? If you back up the log every 5 minutes, will just having the last 100 entries be enough? What happens if you have 10 different backup jobs, or some jobs have a lot of steps? They’re going to start trampling all over each other’s histories.
Backups are not the best example because this information is also stored in msdb. However, it’s an obvious illustration to demonstrate: how we make agent history more customizable.
Solution
Before we get to a solution, let’s dive a little deeper into the problem.
In addition to needing flexibility between jobs and across the whole system, you also might need flexibility between the steps of a job. Consider the case where you have a job with five steps, but only one of them is really important (maybe the others are just initialization steps or send e-mail on success or failure). In the current design, there is no way to say keep the history for step 3, but not the others. If you think it’s just noise, it’s more than that: I set the max history rows to 10, and the max rows per job to 10. Then I created two jobs, one with 1 step, and the other with 5. I ran the first job twice, then the second job twice, and inspected the history.
Note that the job itself (step 0) takes up a history slot, so for the most recent execution of "Job with 5 steps", there are actually 6 history entries. Since we can only keep 10 history rows for any single job, the earlier execution gets truncated. SQL Server holds onto step 0 but deletes the history for steps 1 and 2.
There is an also issue if you only want to keep an 8- or 12-hour rolling window of job history. Maybe you have jobs that run more frequently during business hours, or get called on demand, so they are particularly volatile and/or volume-driven. Limiting to a specific number of rows or to the lowest granularity of a day boundary fail to meet the requirements exactly.
Finally, there may be scenarios where you want to keep all failures around regardless of age or how many times the job or step has succeeded since, and you may want to keep any job or step that has exceeded some specified duration (but not all the other steps that succeeded or ran quickly).
From all of that, we get a simple list of requirements. We want a job history retention policy that allows us to:
- define retention history per job and per step
- keep as many or as few rows as we want
- keep rows for as short or as long as we want
- when rules for both are present, dictate which one "wins"
- keep history around for any step that has failed, even outside of our rules, or that has exceeded some specified duration
Let’s start with a table in msdb that can store details about our preferences (basically the structure of the rules around which rows to keep/delete). Most columns are named intuitively:
USE msdb;<br />GO
CREATE TABLE dbo.RetentionRules<br />(<br /> job_id uniqueidentifier NOT NULL, <br /> step_id int NOT NULL,<br /> NumberOfRowsToKeep int,<br /> NumberOfHoursToKeep int,<br /> PreferRowsOrHours char(1),<br /> KeepStepsThatFailed bit,<br /> DurationOverrideSeconds int, -- keep any step that ran longer than this<br /> CONSTRAINT PK_JobStep PRIMARY KEY (job_id, step_id),<br /> CONSTRAINT CK_RowsOrHours CHECK (PreferRowsOrHours IN ('R','H'))<br /> /*,<br /> CONSTRAINT FK_RetentionRules_JobSteps<br /> FOREIGN KEY(job_id, step_id) REFERENCES dbo.sysjobsteps(job_id, step_id)<br /> */<br />);
You could put a foreign key here, which I’ve commented out, but you might not want to have job changes blocked by rules surrounding the retention table. You could instead put a DML trigger on sysjobsteps to remind you to check on your retention schedule to ensure a new step isn't slipping through the cracks. Not much of a concern if a step is deleted and you still have retention rules for it, but it is a big deal if you add a new step and suddenly its history grows unbounded. Don’t worry; we’ll create a safety net for cleaning up jobs after a specified amount of time, overriding our rules at a certain point.
Next, if we want to eliminate any caps on jobs or steps (exceeding the max 1,000 row threshold), we need to remove the caps altogether. If you know you only ever want to limit steps to less than this 1,000 row limit, you can leave it in place, but if you want to keep more than 1,000 rows of history for any job, you can remove this cap with the following call:
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows = -1, @jobhistory_max_rows_per_job = -1;
The msdb database suffers from some unfortunate early database design decisions that have remained in place through current versions. The most notable one is the use of integers to store date and time, separately, in the sysjobhistory table. So, I’ve created a function and view to remove those calculations from any of my queries:
CREATE FUNCTION dbo.TVF_Agent_Datetime<br />(<br /> @date int, <br /> @time int, <br /> @duration int<br />)<br />RETURNS TABLE WITH SCHEMABINDING<br />AS<br /> RETURN <br /> (<br /> SELECT <br /> StartDate, <br /> EndDate = DATEADD(SECOND, DurationInSeconds, StartDate), <br /> DurationInSeconds<br /> FROM<br /> (<br /> SELECT <br /> StartDate = CONVERT(datetime, <br /> CONVERT(varchar(4), @date / 10000)<br /> + CONVERT(varchar(2), RIGHT('0' + RTRIM(@date % 10000 / 100),2))<br /> + CONVERT(varchar(2), RIGHT('0' + RTRIM(@date % 100),2)) <br /> + ' ' + CONVERT(varchar(2), RIGHT('0' + RTRIM(@time / 10000),2)) <br /> + ':' + CONVERT(varchar(2), RIGHT('0' + RTRIM(@time % 10000 / 100),2)) <br /> + ':' + CONVERT(varchar(2), RIGHT('0' + RTRIM(@time % 100),2))),<br /> DurationInSeconds = <br /> @duration / 1000000 * 24 * 60 * 60<br /> + @duration / 10000 % 100 * 60 * 60<br /> + @duration / 100 % 100 * 60<br /> + @duration % 100<br /> ) AS x <br /> );<br />GO
CREATE VIEW dbo.JobStepHistory<br />AS<br /> SELECT <br /> h.instance_id, h.job_id, h.step_id, h.run_status,<br /> f.StartDate, f.EndDate, f.DurationInSeconds<br /> FROM dbo.sysjobhistory AS h<br /> CROSS APPLY <br /> dbo.TVF_Agent_Datetime(h.run_date, h.run_time, h.run_duration) AS f;
With that function in place, I only need to create a job with a few steps, and insert retention rules into my table, to start testing out the logic I would use to implement those rules. The job, scheduled to run every minute, has three steps: one that succeeds, one that runs longer than 30 seconds, and one that fails:
After the job executes, we can look at how the data is presented in the view I created:
SELECT TOP (4) * <br /> FROM dbo.JobStepHistory<br /> ORDER BY instance_id DESC;
And then in the native sysjobhistory table:
SELECT instance_id, step_id, step_name, sql_message_id, run_status, message <br /> FROM dbo.sysjobhistory <br /> WHERE instance_id IN (3393,3394,3395,3396)<br /> ORDER BY instance_id DESC;
Now the retention rules come into play, because I want to make sure I capture and retain any time step 2 runs longer than 30 seconds, or any time step 3 fails, but I don’t need to maintain data about all the times step 1 succeeds. So, I’ll put these rows into my RetentionRules table:
DECLARE @job_id uniqueidentifier;
SELECT @job_id = job_id <br /> FROM dbo.sysjobs <br /> WHERE name LIKE N'Job A%';
INSERT dbo.RetentionRules<br />(<br /> job_id, <br /> step_id, <br /> NumberOfRowsToKeep, <br /> NumberOfHoursToKeep,<br /> PreferRowsOrHours,<br /> KeepStepsThatFailed,<br /> DurationOverrideSeconds<br />)<br />VALUES<br /> (@job_id, 0, 100, NULL, 'R', 1, 180), -- keep 100 copies of job outcome<br /> (@job_id, 1, 15, NULL, 'R', 1, 90), -- keep 15 rows for step 1, unless fail / > 90 seconds<br /> (@job_id, 2, 80, 72, 'H', 1, 30), -- keep 80 rows / 72 hours for step 2<br /> -- unless fail / > 30 seconds<br /> (@job_id, 3, 75, NULL, 'R', 1, 90); -- keep 75 rows for step 3, unless fail / > 90 seconds
Now we just need a simple query to apply our rules to history, deleting the rows that are just noise, and keeping the rows that we want, depending on the preferences we’ve defined. Over time, we should see that we always discard history for step 1, and we keep the history for steps 2 and 3. Up to a point, though. I highly recommend enforcing an absolute age cutoff so you’re not keeping history for failed or long-running steps for all of eternity. In this case I’ll define that as 1,000 hours, but you can choose what’s right for you based on your environment.
DECLARE @FallBackAgeInHours int = 1000;
;WITH AllStepZeroes AS<br />(<br /> -- all instances of step zero (outcome)<br /> -- since no relational way to connect instance/steps<br /> -- LEAD() requires 2012+
SELECT instance_id, job_id, StartDate, <br /> NextStart = LEAD(startDate, 1) OVER (PARTITION BY job_id ORDER BY StartDate)<br /> FROM dbo.JobStepHistory<br /> WHERE step_id = 0<br />),
JoinHistoryToRules AS <br />(<br /> -- find all the steps and match them to their retention rules
SELECT h.*, <br /> RowsToKeep = r.NumberOfRowsToKeep, <br /> HoursToKeep = r.NumberOfHoursToKeep, <br /> r.PreferRowsOrHours, <br /> r.KeepStepsThatFailed, <br /> r.DurationOverrideSeconds,<br /> -- simple counter to track number of instances of any job/step combo:<br /> rn = ROW_NUMBER() OVER (PARTITION BY h.job_id, h.step_id ORDER BY h.StartDate DESC)<br /> FROM dbo.JobStepHistory AS h <br /> INNER JOIN dbo.RetentionRules AS r <br /> ON h.job_id = r.job_id<br /> AND h.step_id = r.step_id<br />),
Step1 AS<br />(<br /> SELECT *,
-- determine if we should keep or discard a row:
-- if we have more row_number()s than the number we want to keep:
DiscardDueToNumber = CASE <br /> WHEN rn > RowsToKeep <br /> THEN 1 ELSE 0 END,
-- if we have rows older than our age threshold:
DiscardDueToAge = CASE <br /> WHEN EndDate < DATEADD(HOUR, -HoursToKeep, GETDATE()) <br /> THEN 1 ELSE 0 END,
-- if we want to keep steps that failed:
KeepDueToFailure = CASE <br /> WHEN step_id > 0 <br /> AND run_status = 0 – failed<br /> AND KeepStepsThatFailed = 1 <br /> THEN 1 ELSE 0 END,
-- if we want to keep steps that ran longer than a defined threshold:
KeepDueToDuration = CASE <br /> WHEN DurationInSeconds > DurationOverrideSeconds <br /> THEN 1 ELSE 0 END
FROM JoinHistoryToRules<br />),
Step2 AS<br />(<br /> SELECT DeleteMe = CASE<br /> WHEN <br /> (
-- discard due to age unless we care about number of rows<br /> -- or vice versa. Captures case where both are true, too.
(DiscardDueToNumber = 1 AND PreferRowsOrHours = 'R')<br /> OR<br /> (DiscardDueToAge = 1 AND PreferRowsOrHours = 'H')<br /> )<br /> AND <br /> (<br /> -- if we are keeping due to failure or runtime<br /> -- let's not keep them forever
(KeepDueToFailure = 0 AND KeepDueToDuration = 0)<br /> OR StartDate < DATEADD(HOUR, -@FallBackAgeInHours, GETDATE())<br /> )<br /> THEN 1 ELSE 0 END, *<br /> FROM Step1<br />),
Step3 AS<br />(<br /> SELECT *,
-- keep step 0 if we've kept any steps from that instance
KeepStep0 = CASE WHEN step_id = 0 AND NOT EXISTS<br /> (<br /> SELECT 1 FROM AllStepZeroes AS w <br /> WHERE w.job_id = Step2.job_id <br /> AND Step2.step_id > 0 <br /> AND Step2.DeleteMe = 1<br /> AND Step2.StartDate >= w.StartDate <br /> AND Step2.StartDate < w.NextStart<br /> ) THEN 1 ELSE 0 END<br /> FROM Step2<br />)
SELECT *. -- change to DELETE when happy:<br /> --DELETE h<br /> FROM dbo.sysjobhistory AS h<br /> INNER JOIN Step3 <br /> ON Step3.instance_id = h.instance_id<br /> AND Step3.DeleteMe = 1<br /> AND Step3.KeepStep0 = 0;
-- delete all other instances of jobs more than @FallBackAgeInHours old
;WITH LastFullInstanceBeforeCutoff AS <br />(<br /> SELECT job_id, instance_id = MAX(instance_id) <br /> FROM dbo.JobStepHistory<br /> WHERE step_id = 0<br /> AND EndDate < DATEADD(HOUR, -@FallBackAgeInHours, GETDATE())<br /> GROUP BY job_id<br />)<br />SELECT *<br /> --DELETE h <br /> FROM dbo.sysjobhistory AS h<br /> INNER JOIN LastFullInstanceBeforeCutoff AS l<br /> ON h.job_id = l.job_id<br /> AND h.instance_id <= l.instance_id;
When you are happy that this identifies the rows you want to delete from history, and ignores the ones that should be kept, change the two post-CTE SELECT statements to DELETE, and schedule it to run as frequently as you want to clean up the history table. If you schedule this using SQL Server Agent, make sure you go back and add that job to your rules, otherwise you’ll be keeping that history forever.
Future Considerations
You could get even more complex.
Jobs fail for all kinds of reasons, and sometimes further troubleshooting is necessary, and other times it is not. Since sysjobhistory has sql_message_id, indicating the error message, you could keep history for one type of exception, and discard it for another. And perhaps how long you want to keep history changes over the weekend, where you have less volume but want to see on Monday everything that happened since Friday. So, you could add conditional rules for how to handle history on a Friday (keep for 96 hours), Saturday (keep for 72 hours), or Sunday (keep for 48 hours). And of course, you could make it very easy to delete job history once you’ve reviewed and/or resolved the issue. There are a lot of possibilities here; if you have additional rules you’d like to apply, mention them below, and I’ll do what I can to address them.
Summary
Maintaining job history using the native settings can be rather limiting. Creating your own rule system and applying it takes a little bit of thought, but can be very helpful in the long run, keeping only the history you care about and eliminating all the noise.
Next Steps
Read on for related tips and other resources:
