SQL Server Agent Job History Log Not showing any rows

08 December,2017 by Jack Vamvas

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)


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server Agent Job History Log Not showing any rows


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer