info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



Find Microsoft SQL Server Agent Job History Retention Settings using T-SQL
by BF (Principal Consultant; Architecture; Engineering)
2015-11-07







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