Sql-server – Can we restrict Maximum Job history rows for particular job/jobs in SQL Server 2012

sql serversql-server-2012

I am aware that there are 2 universal settings for Job history retention rows in SQL Agent. First one is default 1000 rows and per job max history row count is 100. Second one is deleting history older than given weeks.

Apart from these 2 options, do we have any possibility left to set up our desired max row count for a single job/few jobs? Like individual max row count for each job/specific jobs.

For example, I have a job which runs for every hour whose run time changes according to other concurrent jobs resource blocking.I want to see at which hour schedule, the Job took long time. As I have multiple jobs running on server, only 10 job history entries are available for this job. Also, I don't require more history entries for few other jobs. So, Could anybody please let me know if there is any way to setup individual max history row count for specific jobs?

Best Answer

Not through the UI, but since the sysjobhistory table is not a system table, you could manage this manually with a simple scheduled job that cleans up. Assuming you always want to keep fewer rows for the exceptions than the rest.

First, let's assume you have some query against sysjobs that will return the jobs you want to be more restrictive:

SELECT job_id FROM dbo.sysjobs WHERE <...>;

Next, for simplicity, let's say that for all the jobs in that list, you only want to keep the 10 most recent rows. 

DECLARE @rows_to_keep int = 10;

;WITH history AS
(
  SELECT rn = ROW_NUMBER() OVER 
    (
      PARTITION BY job_id 
      ORDER BY run_date DESC, run_time DESC
    ),
    job_id, run_date, run_time
    FROM msdb.dbo.sysjobhistory
    WHERE job_id IN 
    (
      -- query from above
      SELECT job_id FROM dbo.sysjobs WHERE <...>
    )
)
DELETE history
WHERE rn > @rows_to_keep;

It gets a little more complex depending on the answers to these comments:

If you have two jobs where you want to keep only the last 10 rows, and one job has 2 steps and the other has 5 steps, are you keeping 10 rows each (so 5 executions of the first job, and 2 executions of the second), or 10 total executions each (so 20 rows for the first job, and 50 rows for the second)? Well, disregarding step 0 for simplicity.

Also if you want different rules for different jobs, you can just create your own table:

CREATE TABLE dbo.CustomJobRetentions
(
  job_id uniqueidentifier,
  rows_to_keep int
);

Then you can incorporate that into your query:

;WITH history AS
(
  SELECT rn = ROW_NUMBER() OVER 
    (
      PARTITION BY h.job_id 
      ORDER BY h.run_date DESC, h.run_time DESC
    ),
    h.job_id, h.run_date, run_time, c.rows_to_keep
    FROM msdb.dbo.sysjobhistory AS h
    INNER JOIN msdb.dbo.CustomJobRetentions AS c
    ON c.job_id = h.job_id
)
DELETE history
WHERE rn > rows_to_keep;

Please try this out on a test system first.

Related Question