Advertisements

Archive

Archive for July 17, 2017

SQL DBA – SQL Agent Job history not showing or vanishing away

July 17, 2017 Leave a comment

 
This happened when we started working on a new SQL Server instance for our DEV environment. The history of SQL jobs was not getting retained after a day or few runs.

I checked on the SQL Agent Properties and found that there are some config values set which were very low:
jobhistory_max_rows = 10000
jobhistory_max_rows_per_job = 100

So I checked on MSDN and found that the max permissible values and set them to 999999 & 49999 respectively.

–> This can also be changed by below T-SQL Query by using the system SP sp_set_sqlagent_properties:

USE [msdb]
GO

EXEC msdb.dbo.sp_set_sqlagent_properties 
	@jobhistory_max_rows=999999, 
	@jobhistory_max_rows_per_job=49999
GO

Advertisements
Categories: DBA Stuff Tags: ,