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

This blog has moved! You can find this content at the following new location:

http://sqlblog.karaszi.com/keep-more-history-for-your-agent-jobs/

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

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
Privacy Statement