Blocking on sql can occur if the Maximum job history log size is greater than 1000 on the sql server agent
I had an interesting Biztalk case a short while ago and wanted to make this public knowledge. The Biztalk admins had reported that message delivery was slowing down considerably periodically during the course of operations. They had found that the slowdown could be resolved by simply restarting the sql agent.
We analyzed a sql profiler trace from the envrinment and found that there was blocking occurring in this stored procedure in MSDB
CREATE PROCEDURE sp_jobhistory_row_limiter @job |
SELECT @current_rows_per_job = COUNT(*) FROM msdb.dbo.sysjobhistory with (TABLOCKX) WHERE (job_id = @job_id) ... |
This was being called by a stored procedure called [MsgBoxPerfCounters_GetPurgeJobInfo] which gets run to collect information about the amount of time that each biztalk job is run. This stored procedure creates several temp tables {#Temp, #Final, and #TempHistory} each time it is run and then drops them after selecting a row at a time into them. This is all done to provide information to the perf counters about how long the jobs are running.
When the # of rows in the sysjobhistory table is 1000, this job is generally fast and doesn’t cause any problems, in this case, the # of rows in the job history table was over 50000, and this was causing this blocking chain to occur. After limiting the size of the job history table in the Sql Server Agent Properties, the blocking no longer occurred. This is much more likely to affect performance in a Biztalk environment if there are multiple messageboxes and/or if there are a large # of hosts.