THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Keep more history for your Agent jobs

SQL Server Agent is in my opinion way too restrictive when it removes old job history information.

This information is store in the dbo.sysjobhistory table in the msdb database. Unfortunately, Agent will by default only keep 1000 rows in this table. I have on many occasions wanted to for instance check last execution of the weekly job. Or the trend for the weekly job, like how ling it takes to execute. Only to find that the information is no longer there.

I prefer to expand this to 50000 rows in total and 200 rows per job.

You can do this in the GUI: right-click Agent, Properties, History. Or just execute below SQL command on your SQL Servers:

EXEC msdb.dbo.sp_set_sqlagent_properties
 @jobhistory_max_rows=50000
,@jobhistory_max_rows_per_job=200 

Published Sunday, October 16, 2016 3:07 PM by TiborKaraszi
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

jchang said:

every time a job executes, the procedure sp_sqlagent_log_jobhistory is called to record the fact.

this procedure, at the end, calls sp_jobhistory_row_limiter.

if you look at the procedure, there is a moderate to significant overhead depending on the circumstances,

in the post below

http://sqlblog.com/blogs/joe_chang/archive/2013/03/05/job-history-row-limiter.aspx

I suggested to consider outright modifying the msdb system procedure

October 16, 2016 9:20 AM
 

Chris Wood said:

Joe,

I have started to record the biggest consuming queries against master and msdb on an SQL 2012 SP3 AG and see sp_sqlagent_has_server_access runs as often as sp_jobhistory_row_limiter using a little more CPU.

Any thoughts?

October 17, 2016 11:11 AM
 

jchang said:

In my case, the issue was not that the sp_jobhistory_row_limiter call consuming too much cpu, but rather that it was enough to show up on the top 10-20 list. For all normal code, I had made substantial improvement, and I did not want to say this is MS code that we cannot touch, when in fact we can touch it. My assessment was that adhering to the total and per job limits for each job was stupid, lets not make the sp_jobhistory_row_limiter call with each sp_sqlagent_log_jobhistory, but instead do it once per day or even once per hour.

I am a performance consultant and not a regular DBA, so I didn't even know that there was a procedure to set job history limits until Tibor made this post. And its the first time I heard of sp_sqlagent_has_server_acces when you mentioned it. In looking at it, I am thinking if you the DBA know beyond a shadow of doubt which accounts are sysadmin and which are not, you could consider short-circuiting this procedure with the answer instead of going through the complicated process.

As in my post, I was actually more curious in wondering if there was a DBA out there with the guts to modify a (sort-of-)system procedure. I don't have an issue customizing general purpose code to my specific needs.

October 17, 2016 1:55 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Privacy Statement