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

Leave a Reply

Your email address will not be published. Required fields are marked *