Thursday, March 8, 2012

Blocking by sp_sqlagent_log_jobhistory

Hi ,
We have chain of blocking caused by sp_sqlagent_log_jobhistory in the
morning hours when we have lot of jobs to be executed. When I do DBCC
INPUTBUFFER for the process id's blocking/blocked I am getting
EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id = some hex id .
Nothing changed with the job schedules and we never had the problem but
since couple of week things are running slower and processes are being
blocked .
Since all the blocked/blocking process id's are by
sp_sqlagent_log_jobhistory I think there is something going wrong with my
agent when it is trying to log the success/faliure of the job completion.
Any solutions/suggestions will be helpful.
Thanks,
--
Arshad
arshadmd-nospam@.gmail.comArshad wrote:
> Hi ,
> We have chain of blocking caused by sp_sqlagent_log_jobhistory in the
> morning hours when we have lot of jobs to be executed. When I do DBCC
> INPUTBUFFER for the process id's blocking/blocked I am getting
> EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @.job_id = some hex id .
> Nothing changed with the job schedules and we never had the problem but
> since couple of week things are running slower and processes are being
> blocked .
> Since all the blocked/blocking process id's are by
> sp_sqlagent_log_jobhistory I think there is something going wrong with my
> agent when it is trying to log the success/faliure of the job completion.
> Any solutions/suggestions will be helpful.
first, defrag the msdb database, especially the sysjobhistory table.
see if that fixes it.
next, go to sqlagent properties and see how many job histories it's
configured to keep. maybe you have it set too high, i think 1000 is the
default, i often run mine at 100,000 with 10,000 per job and have no
problems, but i rarely have 2 or 3 jobs running at the exact same time.
you could also try clearing the log on that same tab where you tell it
how many histories to keep.
maybe you have your msdb database on a bad/slow disk?

No comments:

Post a Comment