Missing SQL Server Agent History

By:   |   Comments (4)   |   Related: 1 | 2 | 3 | 4 | > SQL Server Agent


Problem

SQL Server Agent is a useful tool to allow you to schedule reoccurring jobs to run on your server.  Most of these jobs are usually maintenance type jobs or data loads, but this tool can be used to run any T-SQL code or even execute programs external to SQL Server.  In addition to the ability to schedule jobs to run, SQL Server Agent also gives you the ability to see historical run information for the jobs as well as for each step within the jobs.  This information is very helpful to have on hand for knowing if the job succeeded as well as how long the job took to run. This way the next time you need to run one of these jobs you have an idea of how long it will take to complete.  If you have jobs that run on a frequent basis or if you have a lot of jobs that are scheduled you may notice that there is not much historical information stored when you view job history, so what can be done to keep more historical run information?

Solution

In SQL Server 2000 and 2005 the SQL Server Agent has a default setting for the maximum number of history records to store for all job runs as well as the maximum number of history rows to store per job.  As you can see in the next two screen shots the "Job history log" default settings are to store a maximum of 1000 rows for all job history and store a maximum of 100 rows per job.  Once these numbers are exceeded SQL Server will remove the older records to keep a maximum of 1000 rows.  Therefore if you have a few jobs that execute very frequently and other jobs that are run only monthly or quarterly, SQL Server will flush out the older data and therefore you will not be able to see the older historical run data.

SQL 2000

jobhis1

SQL 2005

jobhis2

These two values can be changed to any value that makes sense for you environment. The maximum number of rows per job is probably a sufficient number, but the overall job history rows is probably too small for most environments and this number should probably be increased to a higher value.  Again this depends on how many jobs you have and also how frequently the jobs run.

In addition, to setting either the Max Job History or the Max Job History Per Job to higher numbers you also have the ability to turn these off completely and not have SQL Server enforce any limitation. This may sound like a good thing, but if you have a job that runs every minute this will consume a lot of space for all of these job runs, so you need to be aware of how often your jobs are running and also how much job history you really want to keep.  Also, with no limitations the history table will grow forever.

A couple of other options on this screen are with SQL Server 2000 you have the ability to clear the entire job log from this screen, so you can clear out all historical run data.  With SQL Server 2005 you now have the ability to set a value for how old you want to keep historical data.  This will allow you to trim and manage your SQL Server Agent history log.

 

Where is this data stored?
As you can see this is pretty straight forward to make these adjustments using the GUI.  Often though DBAs need to do this on multiple servers and creating and running T-SQL code on all of these machines is often a better approach. This allows you to store your scripts and have a record of what and when things were run.  Most of the components that SQL Server uses are stored in one of the system databases and because of this changes can be made either using the management tools or by issuing T-SQL statements.  This is the case for most system values, but this is not the case for the job history settings. 

If your run profiler and then make these changes via the GUI you can catch the commands that SQL Server is issuing to read and update these values.  You will notice that the data is not stored in a table, but is stored in the registry.  SQL Server uses two extended stored procedures xp_instance_regread and xp_instance_regwrite to read and write these values within SQL Server.

Here is the command that is used to read the JobHistoryMaxRows value:

jobhis3

Here is the command that is used to read the  JobHistoryMaxRowPerJob value:

jobhis4

Here is the command that is used to update the JobHistoryMaxRowPerJob value:

jobhis5

Here is the command that is used to update the JobHistoryMaxRows value:

jobhis6

As you may have noticed there are just two values JobHistoryMaxRows  and JobHistoryMaxRowsPerJob.   When you unselect the "Limit size of job history log", SQL Server will update JobHistoryMaxRows JobHistoryMaxRowsPerJob in the registry.

For SQL 2000 the JobHistoryMaxRows is set 4294967295 and JobHistoryMaxRowsPerJob is left unchanged.

jobhis7jobhis8

For SQL 2005 the JobHistoryMaxRows is set 4294967295 and JobHistoryMaxRowsPerJob is changed to 0.

jobhis9jobhis10

When the "Limit size of job history log" is selected again these values revert back to the default values.

 

Using the XPs
To query the values using the extended stored procedures you can issue the following queries.

This query returns the JobHistoryMaxRows

DECLARE @jobhistory_max_rows INT
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'JobHistoryMaxRows',
@jobhistory_max_rows OUTPUT

PRINT @jobhistory_max_rows

This query returns the JobHistoryMaxRowsPerJob

DECLARE @jobhistory_max_rows_per_job INT
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'JobHistoryMaxRowsPerJob',
@jobhistory_max_rows_per_job OUTPUT

PRINT @jobhistory_max_rows_per_job 

To update JobHistoryMaxRows value

EXECUTE master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'JobHistoryMaxRows',
N'REG_DWORD',
100000

To update JobHistoryMaxRowsPerJob value

EXECUTE master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'JobHistoryMaxRowsPerJob',
N'REG_DWORD',
100

Although this is pretty straightforward I would recommend using the GUI to make these changes unless you are totally comfortable with using the extended stored procedures.

Next Steps
  • Next time you notice that you are missing job history, check these settings to see if you have the default values still set
  • Look at using the new feature in SQL 2005 to remove older data based on a date setting
  • Although there is not a simple way to change these values on all systems it is possible to use the extended stored procedures to do the update for you.
  • Review these other SQL Server Agent tips


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, October 26, 2015 - 1:55:39 PM - Greg Robidoux Back To Top (38965)

David,

I just checked SQL Server 2014 and this still works the same way.

These values are set per instance.  The examples above are looking at the default instance.

For a named instance MSSQLServer would be the instance name instead.

-Greg

Monday, October 26, 2015 - 12:27:08 PM - David C House Back To Top (38964)

 

I find it odd that Microsoft would store this information in the Registry instead of in the MSDB database. The fact that they are using the  N'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent' key at the OS level causes this to alter the values of every instance on the server. Not what most DBA's would want!

 

Tuesday, January 28, 2014 - 4:33:59 AM - Guest Back To Top (29249)

Notes: Up to at least 2008 R2 SP2, handling large histories is subject to a few bugs/unfortunate design:

* The Job history viewer in SSMS is subject to a timeout, which is quickly too short, and cannot be set to longer values

* The Job history viewer accepts filters, and there are provisions (parameters in the system SP's) to apply these to the source data upon retrieval, but they are circumvented. Filtering helps nothing with the timeout.

* Worst-practice use of GUID keys slow down performance massively

* The system SP's copy data back and forth to tempdb multiple times

* The above is so pronouned a manual select can retrieve the same amount of history up to 4 orders of magnitude faster

Tuesday, July 31, 2012 - 1:48:46 PM - Danette Riviello Back To Top (18862)

Greg- this is great - I am so glad you shared it.   Using what you had here, I put together this little script to boost the max history to current number of jobs times the rows per job plus 10%:

DECLARE @jobhistory_max_rows INT
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'JobHistoryMaxRows',
@jobhistory_max_rows OUTPUT

DECLARE @jobhistory_max_rows_per_job INT
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'JobHistoryMaxRowsPerJob',
@jobhistory_max_rows_per_job OUTPUT

declare @numjobs int
select @numjobs = count(*)
from msdb.dbo.sysjobs

select @jobhistory_max_rows_per_job "CurrentRowsPerJob",
@numjobs "CurrentNumberOfJobs",
@jobhistory_max_rows_per_job*@numjobs "RowsbyJobs",
@jobhistory_max_rows "CurrentMax",
@jobhistory_max_rows_per_job*@numjobs*1.1 "NewMax"

declare @newjobhistory_max_rows_per_job int

if @jobhistory_max_rows_per_job*@numjobs>@jobhistory_max_rows
begin
 set @newjobhistory_max_rows_per_job = @jobhistory_max_rows_per_job*@numjobs*1.1
 
 EXECUTE master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
 N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
 N'JobHistoryMaxRows',
 N'REG_DWORD',
 @newjobhistory_max_rows_per_job
end














get free sql tips
agree to terms