In Microsoft SQL Server, these retention settings are configurable via SQL Server Management Studio in SQL Sever Agent properties.
The values are set in the Server Registry and available using T-SQL. This is quite useful if required to obtain
the values over numerous SQL Server installations.
Solution:
Query #1: Get Maximum Jog History Log Size:
DECLARE @msdb_jobhistory_max_rows INT = NULL
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'JobHistoryMaxRows',
@msdb_jobhistory_max_rows OUTPUT,
N'no_output'
SELECT @msdb_jobhistory_max_rows as 'JobHistoryMaxRows'
Query #2: Get Maximun Job History Rows Per Job:
DECLARE @msdb_jobhistory_max_rows_per_job INT = NULL
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'JobHistoryMaxRowsPerJob',
@msdb_jobhistory_max_rows_per_job OUTPUT,
N'no_output'
SELECT @msdb_jobhistory_max_rows_per_job as 'JobHistoryMaxRowsPerJob'
Image #1: Registry
|
|
|
|
|
|