FROM msdb.dbo.sysjobhistory with (TABLOCKX)
WHERE (job_id = @job_id)
Notice the TABLOCKX? That is the problem. It essentially serializes all access against that table. So, if you have a lot of frequently-executed jobs you can see that this will become a bottleneck and will block.
The code I have created (available here for download) works around these issues by:
- turning off job history log limits if they are enabled. This means YOU are responsible for purging.
- Creates a new table that allows you to specify, at the JobName or Category level, exactly how many rows you wish to maintain
- You can also specify a (default) value that will be applied when the JobName or Category does not have an entry.
- We don't actually target a specific number of rows to maintain, rather, a number of days of history to maintain. Logically, I think it makes more sense to maintain "days of history" vs number of rows. This is just my opinion. It's also a little bit more efficient code since I can just purge by date vs getting a target number of rows to maintain.
- We can target different "days to maintain" for success vs failure rows. This is important. If I run a job every minute I may not care about saving ANY successes, but I may want to see multiple years' worth of failures.
- Runs as its own sqlagent job. I would have rather made this a Service Broker "task" but oh well.
I have also included a series of TSQLT tests. These are database unit tests. I feel that all code should be unit tested not just to prove that it works, but also as a self-documenting tool. There are a lot of nuances in this code and I have them well-documented in the unit tests.
I hope someone finds this code useful.