SQL Server Agent Job History Log Not showing any rows

Question: I’m just investigating a SQL Server Agent Job failure and noticed there were no records in the SQL Server Agent history log.

I was surprised as the job has been running .

Can you explain to me how I can investigate why my SQL Server Agent job history log is not displaying?

Answer: It is possible a process has cleared out the msdb.dbo.sysjobhistory table. You can check the table by

                    select * from msdb.dbo.sysjobhistory

If statement returns 0 records , than investigate how and why the rows have been deleted.0 records suggests ALL the sql server agent job history log rows are deleted.

If greater than 0 records , than it is likely the reason is related to the configurations on the SQL Agent jobs. i.e @jobhistory_max_rows

There are couple of different variables to discuss. Both of these variables can be adjusted according the system requirements:

@jobhistory_max_rows  = The maximum rows in job history for ALL jobs

@jobhistory_max_rows_per_job = The maximum rows in a job for a single job

DECLARE @jobhistory_max_rows INT = NULL,
        @jobhistory_max_rows_per_job INT = NULL
 
 
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
                                        N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                        N'JobHistoryMaxRows',
                                        @jobhistory_max_rows OUTPUT,
                                        N'no_output'
SELECT @jobhistory_max_rows = ISNULL(@jobhistory_max_rows, -1)
 
 
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
                                        N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                        N'JobHistoryMaxRowsPerJob',
                                        @jobhistory_max_rows_per_job OUTPUT,
                                        N'no_output'
 
 
SELECT @jobhistory_max_rows, @jobhistory_max_rows_per_job

You can quickly see there will be an issue if you have a multiple small SQL Agent jobs that run every few minutes – they could consume all the SQL Server Agent job history log rows. Then if you have a SQL Server Agent job running once a week, that could easily disappear from the SQL Server Agent  job history log.

So the key is to understand how many SQL Server Agent jobs are running relative to the @jobhistory_max_rows   .

To make the adjustments use these stored procedures.

--adjust SQL Server Agent job history maximum rows
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=10000
GO
--adjust job history max rows PER job
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows_per_job=101
GO

 Read More on SQL Server Agent Jobs

 Send sql server agent job history through email notification

Manage SQL Agent jobs without sysadmin (SQL Server DBA)

SQL Agent Jobs – Schedule in seconds (SQL Server DBA)