SQL Server 2022: Blocking Issues with sp_help_jobhistory and sp_jobhistory_row_limiter Procedures

Question

Hello All,

I'm experiencing persistent blocking issues in SQL Server 2022, specifically related to the sp_help_jobhistory and sp_jobhistory_row_limiter procedures. Here’s the background and the steps I've taken so far:

Background:

• SQL Agent History Settings: Maximum job history log size set to 9,999,999 rows and maximum job history rows per job set to 2,000 rows.
• High-Frequency Jobs: Several jobs run every minute or even more frequently, causing these procedures to run very often.

Issues:

1. Blocking with sp_help_jobhistory: Frequent execution of this procedure is causing lead blocks, impacting performance.
2. Blocking with sp_jobhistory_row_limiter: This procedure, which maintains job history within configured limits, is also causing blocks.

Steps Taken:

Reduced Job History Size:

EXEC msdb.dbo.sp_set_sqlagent_properties 
    @jobhistory_max_rows = 100000, 
    @jobhistory_max_rows_per_job = 1000;

Optimized Indexes:

CREATE INDEX idx_job_id ON msdb.dbo.sysjobsteps(job_id);
CREATE INDEX idx_job_id_step_id ON msdb.dbo.sysjobhistory(job_id, step_id);
CREATE INDEX idx_job_id_run_date ON msdb.dbo.sysjobhistory(job_id, run_date);

1. Are there any additional optimizations or best practices to reduce blocking issues with sp_help_jobhistory and sp_jobhistory_row_limiter?
2. Is there a way to further optimize the job history maintenance process to avoid conflicts and improve performance?
3. Are there any known issues or limitations with SQL Server 2022 related to these procedures?

Any insights or suggestions would be greatly appreciated!

Answer 1

check if you have sql maintenance job that is triming the sql agent log . Remove that job.

Remove all the custom code and script from msdb . It is not necessary to add indexes to system tables. Remove thos indexes. Restore all sys procedures and tables to original state.