Checking job history log size with TSQL

One of the important settings on SQL Server is the job history log size and the maximum rows per job. Without these set accurately you may not be able to track down job failure issues.

These are easy to locate through the SSMS dialogs, but if you are managing many servers you may not want to dig through all the dialogs to find these values. There is a quicker way, and that is to just read the values for the servers registry.

DECLARE @max_rows as INTEGER = 0;
DECLARE @max_rows_per_job as INTEGER = 0;

EXECUTE master.dbo.xp_instance_regread 
	N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
    N'JobHistoryMaxRows',
    @max_rows OUTPUT,
    N'no_output';

EXECUTE master.dbo.xp_instance_regread 
	N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
    N'JobHistoryMaxRowsPerJob',
    @max_rows_per_job OUTPUT,
	N'no_output';

select @max_rows as MaxRows, @max_rows_per_job as MaxPerJob;

When run it will look something like this:

Then if you decide you need to change adjust the numbers, you can use the stored procedure sp_set_sqlagent_properties to set the new values.

EXEC msdb.dbo.sp_set_sqlagent_properties 
    @jobhistory_max_rows = 1000,
    @jobhistory_max_rows_per_job = 100

These queries have certainly helped me. Enjoy!

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!