Reporting services scheduled reports are clogging up sql job history log

Question

This is a bit of a cross-disciplinary question - Hopefully I’ve posted in the right place.

SQL server 2008 R2

Question: How can I limit the number of rows in [sysjobhistory] for specific jobs, especially jobs inserted into SQL Agent from High Frequency Reporting Services Subscriptions?

History: I have a SQL server that where the SQL Agent has to handle a mix of ETL jobs and jobs coming from reporting services schedules. Recently I am finding that I only have 2 or 3 days of history on the ETL jobs where I used to have 5-7 days or more. The SQL agent properties for Job History retention are:

Maximum Job History Size (in rows): 5000
Maximum Job History rows per job: 1000

If you were thinking “reporting services jobs are going to be the problem…” you would be right. We recently introduced some reporting services reports that are responsible for populating data for overhead displays, running at 3 min intervals all day. Of course these jobs are hitting their max rows per job (1000) in no time flat and leaving only 1000 rows for all the other jobs total.

I don’t want to change the SQL Agent Job History row limits – but I would like to have longer histories on the ETL jobs.

The Reporting services DB carries its own logs internally (ExecutionLog), so I don’t really need 600+ log entries per day per job in sysjobshistory for these High Frequency subscriptions.

The Jobs inserted into SQL Agent by Reporting Services scheduler specifically warn against modifying them outside of Report Manager..but as far as I can see the Report scheduler has no options for this.

Answer 1

Write your own job that clears out the entries (and its own!) every couple of minutes.

You do have the ability to clear out history details by simply issuing:

DELETE msdb.dbo.sysjobhistory WHERE run_status = 1 AND job_id = 'guid';